• 517K Members
• 4,379 Online
• 615K Conversations
SOLVED

## Understanding Datedif regarding days

Occasional Contributor

# 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...

10 Replies
Solution

# Re: Understanding Datedif regarding days

Hi

I created a video tutorial about the DateDiff Function in Excel

You can watch it here

Hope that Helps

How Long an employee has been working for a company? How long our beloved ones lived? What is the age of person in years, months & days? The hidden function ...

# Re: Understanding Datedif regarding days

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"``

# Re: Understanding Datedif regarding 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"

# Re: Understanding Datedif regarding days

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"``````

# Re: Understanding Datedif regarding days

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 @Sergei Baklan

or

Learn about the function in details from my tutorial in the previous reply

Thanks

# Re: Understanding Datedif regarding days

This one work!!!! Thank for all your great information.

# Re: Understanding Datedif regarding 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.

# Re: Understanding Datedif regarding days

You can use below :

=IF(C8="","",DATEDIF(C8,TODAY(),"y")&" years, "&DATEDIF(C8,TODAY(),"ym")&" months, "&DATEDIF(C8,TODAY(),"md")&" days")

Thanks

Tauqeer

# Re: Understanding Datedif regarding days

Yea again..I am so excited.  Thank YOU!!!!

# Re: Understanding Datedif regarding days

You are welcome    @frontdesksm