1. ## counting days (2007)

G'day:
I have a spreadsheet which has a "buy date" cell, and a "sell date" cell. I am in need of a counter formula, which tells me how many days I have held a product, or stock item.
Appreciate what you blokes do here for us "Not-so-literate" persons.
cheers

2. ## Re: counting days (2007)

Let's say that the buy date is in cell B1 and the sell date in cell B2.
In the cell where you want to display the number of days, enter the formula <code>=B2-B1</code>
The result will look strange because Excel formats the result as a date.
Click the dropdown arrow on the right hand side of the Clear button (on the Editing section of the Home ribbon).
Select Clear Formats from the dropdown menu.
You'll see the difference between the dates in days.

Explanation: dates are stored in Excel as a number of days (since 1/1/1900), so you can simply subtract them.

3. ## Re: counting days (2007)

Hi Kio

One option is to use the datedif function

so it would like this =datedif(A1,A2, "d")

The d signifies the number of days

4. ## Re: counting days (2007)

Hans: Thank you!
I was thinking It just HAD to be so damned long and complicated.. Me of little faith!
B2 and B3 cells were both formatted to the same date format, and the target cell was formatted to "numbers" with no decimal points ticked..and lo and behold.. did the job.
Cheers

5. ## Re: counting days (2007)

Further to the chart, to eliminate the ####### or #value# on incompleted orders, the formula reads
=IF(L2<>"",(K2-C2),"")
where L2 is the sold unit cell K2 is the sold date cell and C2 is the bought date cell
a profit in percentage format in the previous cell reads
=IF(L2<>"",(R2/J2),"") and a profit in dollars format reads
=IF(L2<>"",(Q2-J2),"") Which is where I got a tad mixed up, because it is the same as the "days held" cell - but by changing the column format to relevant values gives me everything I need.... (For now)
cheers [img]/forums/images/smilies/smile.gif[/img]

6. ## Re: counting days (2007)

Jezza: thank you for that..
I have added that to my library also.. tried it, and it seems to work ok too ..
cheers

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•