DATEDIF function.... wrong results since new years..?

I assume you are using Excel 2007 - there's a bug with DATEDIF in that version (which affects "md" argument)

Instead of

=DATEDIF(G7, NOW(), "MD")

try using this formula

=TODAY()-MIN(DATE(YEAR(TODAY()),MONTH(TODAY())-(DAY(TODAY())< DAY(G7))+{1,0},DAY(G7)*{0,1}))

barry houdini said:

I assume you are using Excel 2007 - there's a bug with DATEDIF in that version (which affects "md" argument)

Instead of

=DATEDIF(G7, NOW(), "MD")

try using this formula

=TODAY()-MIN(DATE(YEAR(TODAY()),MONTH(TODAY())-(DAY(TODAY())< DAY(G7))+{1,0},DAY(G7)*{0,1}))

Click to expand...

This seems to be an accurate replacement for the DATEDIF value, Thank you.
Any idea why I have a value of 3 if 'G#' (Hire date) is blank?

In your opinion, should I also be changing my DATEDIF formulas for "Y" & "YD", just to elimate the use of DATEDIF?

Thanks,

** Also, what would that formula work out to be for finding the DATEDIF "YD" value? as this is also giving me incorrect values (just noticing)

DATEDIF should be OK for "y", "m", "d" or "ym" - the problematic ones are "md" and "yd".

This version should be simpler for "md"

=TODAY()-EDATE(G7,DATEDIF(G7,TODAY(),"m"))

That will also give 3 if G7 is blank - avoid that by changing to

=IF(G7="","",TODAY()-EDATE(G7,DATEDIF(G7,TODAY(),"m")))

working on "yd".......

Try this instead of "yd"

=IF(G7="","",TODAY()-EDATE(G7,DATEDIF(G7,TODAY(),"y")*12))

It won't always exactly match results from DATEDIF (but to me the results make more sense)

barry houdini said:

Try this instead of "yd"

=IF(G7="","",TODAY()-EDATE(G7,DATEDIF(G7,TODAY(),"y")*12))

It won't always exactly match results from DATEDIF (but to me the results make more sense)

Click to expand...

Thanks Barry,
This appears to be a suitable replacement as well. This one was calculation for sending out a notification email 2 weeks prior to an employees Birthdays.

Thank you for the help on those, EDATE is new to me... I haven't seen or used that formula before. Seems to be fairly straight forward though.

Appreciate the prompt, and helpful reply.

Thanks,

I set this up and if I set a start date of 2001-04-01 and use NOW() "2012-01-03" I get "2". Which is what I'd expect. Do you get something different?

Tardis said:

I set this up and if I set a start date of 2001-04-01 and use NOW() "2012-01-03" I get "2". Which is what I'd expect. Do you get something different?

Click to expand...

Tardis - Sorry, Unfortunately I don't understand what you're trying to ask.

Barry - I'm just working now on correcting all the formulas that went sour in my workbook due to the DATEDIF glitch I got in 2012 New Year. I have one complex IF statement containing a couple of DATEDIF's that I'm having trouble re-writing and achieving correct results. Are you interested in some reverse engeneering? see if you have a better suggestion?
You seemingly have a good grasp on working with dates.

Here's the IF statement, obviously I can explain it further. It's a bit clumsy, but was the best I could figure at the time that I had written it.
=IF(I7>0,IF(365-DATEDIF(G7,NOW(),"yd")<=14,"1","0"),IF(365-DATEDIF(G7,NOW(),"yd")<=14,"1",IF(K7=5,IF(M7>15,"2","0"),IF(K7=2,IF(M7>15,"3","0"),"0"))))

I attempted working EDATE into it, just not achieving proper results myself.

Let me know. Thanks.

I assume you are using this part

=365-DATEDIF(G7,NOW(),"yd")

to get days until next birthday. Because of the use of 365 that won't always be accurate....even if the DATEDIF result with "yd" was correct....so this is a better formula for giving an accurate number of days until next birthday

=EDATE(G7,DATEDIF(G7,TODAY()-1,"y")*12+12)-TODAY()

[on birthday itself that will return zero]

so employng that in your formula I think you can use this:

=IF(EDATE(G7,DATEDIF(G7,TODAY()-1,"y")*12+12)-TODAY()<=14,1,IF(I7>0,0,IF(M7>15,IF(K7=5,2,IF(K7=2,3)))))+0

Note: you don't normally put the numbers in quotes (that makes them into text values).

barry houdini said:

I assume you are using this part

=365-DATEDIF(G7,NOW(),"yd")

to get days until next birthday. Because of the use of 365 that won't always be accurate....even if the DATEDIF result with "yd" was correct....so this is a better formula for giving an accurate number of days until next birthday

=EDATE(G7,DATEDIF(G7,TODAY()-1,"y")*12+12)-TODAY()

[on birthday itself that will return zero]

so employng that in your formula I think you can use this:

=IF(EDATE(G7,DATEDIF(G7,TODAY()-1,"y")*12+12)-TODAY()<=14,1,IF(I7>0,0,IF(M7>15,IF(K7=5,2,IF(K7=2,3)))))+0

Note: you don't normally put the numbers in quotes (that makes them into text values).

Click to expand...

Thanks Barry. That formula seems to be functioning just fine!
I had originally done those "numbers" as a text value, as it used to have a text result, just neglected to delete the "".

This formula is actually the 'trigger' for the emails that are automatically emailed out. When "0" (no email is sent), When "(1,2,3)" determines which email is sent.

"1" = 14 days before yearly aniversary (however many years employed since hire date)
"2" = 14 days before employed for 6 months since hire date
"3" = 14 days before 3 months probation period ends since hire date

Years, Months & Days since hire date. (Has been employed #years, #months & #days)
G7 = Hire date
I7 =DATEDIF(G7, NOW(), "Y") - (years since hire date)
K7 =DATEDIF(G7, NOW(), "YM") - (months, within year, since hire date)
M7 =TODAY()-EDATE(G7,DATEDIF(G7,TODAY(),"m")) - (days, within month, since hire) <-- this as suggested by yourself, to replace DATEDIF "MD"

Your suggested formula seems to have solved the issues I had with the DATEDIF function.

Thanks so much for the prompt, to the point & accurate suggestions you gave.
The 'Mr. Excel' forum is a very helpful resource because of people like yourself.

Very much appreciated, Thanks.