• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

DATEDIF()

D

daggan

New Member
I am using the DATEDIF() and TODAY functions, using Excel 2007, but there are too many days displayed. For example, my birthday is on 27th December 1940, and it comes up with 75years 0 months 119 days, when it should only be 6 days.
I've tried changing the format around, but with no luck so far.
Can anyone help me with this, please?
 
Can you provide a sample of your spreadsheet?

I also am using Excel 2007. It's got the years correct, but I'm not sure why DATEDIF is not reporting the correct number of days.

=IF(DATEDIF("12/27/1940",TODAY(),"Y") = 0, "", DATEDIF("12/27/1940",TODAY(),"Y") &" years, ")& IF(DATEDIF("12/27/1940",TODAY(),"YM") = 0, "", DATEDIF("12/27/1940",TODAY(),"YM") &" month(s), ")& IF(DATEDIF("12/27/1940",TODAY(),"MD") = 0,"",DATEDIF("12/27/1940",TODAY(),"MD")&" Days")
 
Last edited:
Whatever said:
Can you provide a sample of your spreadsheet?

I also am using Excel 2007. It's got the years correct, but I'm not sure why DATEDIF is not reporting the correct number of days.

=IF(DATEDIF("12/27/1940",TODAY(),"Y") = 0, "", DATEDIF("12/27/1940",TODAY(),"Y") &" years, ")& IF(DATEDIF("12/27/1940",TODAY(),"YM") = 0, "", DATEDIF("12/27/1940",TODAY(),"YM") &" month(s), ")& IF(DATEDIF("12/27/1940",TODAY(),"MD") = 0,"",DATEDIF("12/27/1940",TODAY(),"MD")&" Days")
Click to expand...
@Whatever
You could test to write dates 'dd/mm/yyyy'-format?
=DATEDIF("27/12/1940";TODAY();"MD")
 
Whatever said:
Can you provide a sample of your spreadsheet?

I also am using Excel 2007. It's got the years correct, but I'm not sure why DATEDIF is not reporting the correct number of days.

=IF(DATEDIF("12/27/1940",TODAY(),"Y") = 0, "", DATEDIF("12/27/1940",TODAY(),"Y") &" years, ")& IF(DATEDIF("12/27/1940",TODAY(),"YM") = 0, "", DATEDIF("12/27/1940",TODAY(),"YM") &" month(s), ")& IF(DATEDIF("12/27/1940",TODAY(),"MD") = 0,"",DATEDIF("12/27/1940",TODAY(),"MD")&" Days")
Click to expand...

Whatever, here is a sample of my spreadsheet :
=DATEDIF(B5,$A$1,"y")& " years " & DATEDIF(B5,$A$1,"Ym") &" months "& DATEDIF(B5,$A$1,"md") &" days "
Douglas27/12/194075 years 0 months 119 days.
 
I have been using the datedif() function some years now, for birthday reminders, but have only just noticed the "days" problem. Could a recent upgrade from Windows 7 to Windows 10 have any effect?
 
Hi @daggan

You can try the following alternate as explained here:

=INT(($A$1-B5)/365.25)&" years, "&INT(MOD(($A$1-B5)/365.25,1)*12)&" months and "&INT(MOD(($A$1-B5)/30.4375,1)*30.4375)&" days"

Regards,
 
@daggan
Have You tried to write dates (day/month/year)?
Did You tested my file?
Screen Shot 2016-01-04 at 10.28.05.png
... and You're using ',' between B2 & A2 ..
=> 27/12/1940 -> 04/01/2016 >> 75y 0m 8d
 
@Khalid NGO
Interesting ...
our Excel version's are different and You're using "," - I'm using ";".
Maybe, maybe there are some challenges with dates ...
default settings ... who knows/cares?
So, I can use this function so far, but You should be careful.
There would be smaller error with Your shown formula, as You know.
Thanks.
 
vletm said:
@Khalid NGO
Interesting ...
our Excel version's are different and You're using "," - I'm using ";".
Maybe, maybe there are some challenges with dates ...
default settings ... who knows/cares?
So, I can use this function so far, but You should be careful.
There would be smaller error with Your shown formula, as You know.
Thanks.
Click to expand...

Hi vletm,
Yes, we have different versions.
The problem is "MD" or "YM" parameter of DATEDIF function, as mentioned by Narayan Sir in post # 7 above and the link therein, and the link clarifies that there is nothing wrong with default (language) settings.

If you find something relates to our versions, let me share.

Regards,
 
Khalid NGO said:
Hi vletm,
Yes, we have different versions.
The problem is "MD" or "YM" parameter of DATEDIF function, as mentioned by Narayan Sir in post # 7 above and the link therein, and the link clarifies that there is nothing wrong with default (language) settings.

If you find something relates to our versions, let me share.

Regards,
Click to expand...


I went to "Office Button" > [Excel Options] > [Resources] > [About] and found:
Microsoft Office Excel 2007 (12.0.6739.5000) SP3 MSO (12.0.6739.5000)

...and my version doesn't report the correct number of days.
 
Whatever said:
I went to "Office Button" > [Excel Options] > [Resources] > [About] and found:
Microsoft Office Excel 2007 (12.0.6739.5000) SP3 MSO (12.0.6739.5000)

...and my version doesn't report the correct number of days.
Click to expand...
Hi,

Thanks for the info, but actually I asked about the DATEDIF SOLUTION for our versions.
vletm (or some other members) use ";" where we work with ","

Regards,
 
@Khalid NGO - I found this 'normal case':
http://answers.microsoft.com/en-us/...311-b568-57b6b4eee504?page=1&tm=1327393187538
says:
An informal survey of fellow MVPs shows the above formula works correctly in the initial release of XL2007 and its SP1, but does not work correctly in SP2; hence, it appears to be broken at that level. The problem is that the extent of the breakage is unknown (and probably indeterminable). In addition, I would say, being an undocumented (and, thus, probably an unsupported) function, the odds of Microsoft spending the time to search down and fix whatever broke is slim. In addition, again because it is probably unsupported, the extent of any future breakage in the function due to other code change Microsoft makes elsewhere in Excel is unknowable... something that works today may not work tomorrow and Microsoft will probably never fix it. This would seem to mean that DATEDIF cannot be counted on to work correctly from XL2007 SP2 onward. And even if Microsoft did fix the problem in a subsequent Service Pack, any of your users who remained at SP2 would be subjected to incorrect result.
>> Sometimes Service Packs can fix things ... sometimes those can make new features ...
 
So the question now is what about Excel 2010, 2013, 2016, and beyond?

(10 minute time lapse...)
I just heard back from a friend with Excel 2013 and it worked flawlessly.
It was on an older MS Windows Surface Pro 2.
 
Last edited:
@Whatever
Could You try to read that link or/and make 'google-search'?
I would say that next ServicePack could or won't fix this 'challenge' no matter which version You have.
as well
>> Sometimes Service Packs can fix things ... sometimes those can make new features ...
 
You must log in or register to reply here.
Back
Top