Problem Date calculations (cont') Part 2

  • I created a formula that calculates the Start Date and todays date, the formula's goal was to calculate the amount of days from the start date. This formula appeared to be working fine until recently when I checked the stat and is displayed 01 as the number of days.


    F4 is the cell that contains the start date and time 9/10/04 8:00 AM

    The Cell format was 'dd', I also tried 'mm dd' and 'yy mm dd'

    When I added 'mm' to the cell the month was displayed as 02 which is also incorrect.

    Thanks in advance for any help, I'll try to attach a sample of the formula.

  • Hi,

    If I understand you question and the answer you want is 31 days between todays date and the start date, change Now() to Today().

    That gives 31 days as the answer.

    In addition you need to change the format of the cell counting the days to number format.


  • Problem Date calculations (cont') Part 2

    This problem has returned now when using both =Now() and =Today().

    When I originally posted the issue chaning the formula to =TODAY() resolved the problem (The result was no longer 01 it was 31 as it should be, but after that day the results were 02 and I've tried both NOW and TODAY in the formula and cannot get the desired results.

    Thanks in advance for any help

    Attached is a sample of the current formula.

  • Merged the two threads: Slick, just add the post to the original if the subject matter is pretty much the same... then the forum members can see the complete problem/issue... cheers

  • Thanks WillR, that did give me the desired results.

    Could you give me a brief description of the formula.

    My Assumption is "d" means format Day

  • Yeah sure...

    For some starnge reason, the DATEDIF function is not documented anywhere in Excel's help files... I think it was once but is now forgotten :( But the good news is that it is staill available.

    syntax is as follows


    where startdate & enddate are valid dates

    interval as follows

    "m" Months The number of complete months .
    "d" Days The number of days.
    "y" Years The number of complete years.
    "ym" Months Excluding Year The number of months between start and end, as if both dates in the same year.
    "yd" Days Excluding Years The number of days between start and end, as if both were in the same year.
    "md" Days Excluding Months And Years The number of days between start and end, as if both were in the same month and the same year.

    Hope this helps

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!