Forum Discussion
Understanding Datedif regarding days
Look at column C. The days are all 30 days. Why are they all the same?
I agree with year and month but I can not figure out days.
Another example is Today is July 31, 2019 substract from May 4, 2019 should give 0 years, 2 months, 5 days pass! Right or Wrong...
Formula I am using:
=DATEDIF(B2,today(),"y")&" years, "
&DATEDIF(B2,today(),"ym")&" months, "
&today()-DATE(YEAR(today()),MONTH(today()),1)&" days"
A B C
today date 7/31/2019
8/1/2017 1 years, 11 months, 30 days
9/1/2018 0 years, 10 months, 30 days
1/26/2019 0 years, 6 months, 30 days
8/1/2018 0 years, 11 months, 30 days
6/28/2019 0 years, 1 months, 30 days
5/4/2019 0 years, 2 months, 30 days
Please can you help with problems...
Hi
I created a video tutorial about the DateDiff Function in Excel
You can watch it here
https://www.youtube.com/watch?v=9c3DgoTzTU8
Hope that Helps
Nabil Mourad
10 Replies
The DateDif function does not show a tool tip it's an old function that Microsoft kept for backward compatibility, although it works just fine.
So you can either copy and paste the function provided by the Excel Guru SergeiBaklan
or
Learn about the function in details from my tutorial in the previous reply
Thanks
Nabil Mourad
By the way, if to add some cosmetic
=IF(DATEDIF(B2,C2,"y"),DATEDIF(B2,C2,"y") & " years ", "") & IF(DATEDIF(B2,C2,"y")+DATEDIF(B2,C2,"ym"),DATEDIF(B2,C2,"ym") & " months ","") & DATEDIF(B2,C2,"md") & " days"You may also try this formula.
=DATEDIF(B2,TODAY(),"y")&" years, "
&DATEDIF(B2,TODAY(),"ym")&" months, "
&(DATEDIF(B2,TODAY(),"D"))-(DATEDIF(B2,EDATE(B2,DATEDIF(B2,TODAY(),"M")),"D"))&" days"=DATEDIF(C8,TODAY(),"y")&" years, "
&DATEDIF(C8,TODAY(),"ym")&" months, "
&DATEDIF(C8,TODAY(),"md")&" days"If the cell is empty what would I add to the formula to not calculate?
Thank you for all your great information.
You can use below :
=IF(C8="","",DATEDIF(C8,TODAY(),"y")&" years, "&DATEDIF(C8,TODAY(),"ym")&" months, "&DATEDIF(C8,TODAY(),"md")&" days")
Thanks
Tauqeer
In your formula for days part
&today()-DATE(YEAR(today()),MONTH(today()),1)you actually subtract from from TODAY() first day of the current month. If today is 31st it always gives 31-1=30.
I'd use
=DATEDIF(B2,C2,"y") & " years " & DATEDIF(B2,C2,"ym") & " months " & DATEDIF(B2,C2,"md") & " days"Hi
I created a video tutorial about the DateDiff Function in Excel
You can watch it here
https://www.youtube.com/watch?v=9c3DgoTzTU8
Hope that Helps
Nabil Mourad
This one work!!!! Thank for all your great information.
