Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    May 2007
    Location
    desertland, Western Australia, Australia
    Posts
    33
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: counting days (2007)

    Hi Kio

    One option is to use the datedif function

    =datedif([buydate],[selldate], "d")

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

    The d signifies the number of days
    Jerry

  4. #4
    Lounger
    Join Date
    May 2007
    Location
    desertland, Western Australia, Australia
    Posts
    33
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #5
    Lounger
    Join Date
    May 2007
    Location
    desertland, Western Australia, Australia
    Posts
    33
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #6
    Lounger
    Join Date
    May 2007
    Location
    desertland, Western Australia, Australia
    Posts
    33
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
  •