Datedif question

[Theanrkist]

Hello,
I tried searching the forums before posting here but I came up empty handed.

I have been using the formula below since last September without any problems; however, in the past month or so it has gotten to be a day behind (yes the pc clocks are correct).


=DATEDIF(1/1/2009, NOW()-1, "YD")*132


Instead of NOW()-1 i need NOW()-2 to get the appropriate numbers. At first I figured it was a leap year issue since the original date was 2008, so I updated the formula to 2009 and it still is not working. If I manually add up the days in the year and multiply by 132, it's not matching what this formula says.

I have tried the formula on 3 different computers using Excel 2003 and Excel 2000, all with the same results.

I also use the exact formula except with "MD" instead of "YD" and it calculates just fine.

A quick background on the formula:
I work at a hotel and we need a daily total for the year on the number of the rooms we have (132). This spreadsheet is only used the day after hence the NOW()-1.

I'm sure there are other ways to get the correct number, but I am mainly just trying to figure out why this particular formula stopped working.


Thanks in advance
~Chris

[JBeaucaire]

DATEDIF deals with dates only, and NOW() is a Date&Time function. First thing you should do is switch to TODAY() instead of NOW() since TODAY() is a Date only function.

[JBeaucaire]

I declared a Named range called BeginDate set at 1/1/2009 which allows it to be used in formulas and evaluated as if it were a cell value.

Then I adjusted your formulas since there is no need to do the -1 thing, a DATEDIF formula always seems to lose a day...I typically have to ADD it back in with a +1. Since you want the date range to calculate through yesterday, the answer is already correct.

C6:
=DATEDIF(BeginDate, TODAY(), "MD")*B6

D6:
=DATEDIF(BeginDate, TODAY(), "D")*B6

[daddylonglegs]

The problem with the formula

=DATEDIF(1/1/2009, NOW()-1, "YD")*132

is that 1/1/2009 isn't interpreted as a date but as a calculation, i.e. 1 divided by 1 divided by 2009 which evaluates to a small number, i.e. 0.0004977.....

DATEDIF truncates this to zero which in Excel is effectively 31/12/1899. Because this is the last day of the year rather than the first your calculation is 1 out. Try putting the date in quotes, then it'll be recognised as a date, i.e.

=DATEDIF("1/1/2009", NOW()-1, "YD")*132

[Theanrkist]

Thank you both for the very quick responses!

Both of your solutions worked, I can't thank you enough!





~Chris

[zbor]

you can make this thread [solved]... That would be enought thanks

[JBeaucaire]

Glad to help.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]