SOLVED
Home

Understanding Datedif regarding days

%3CLINGO-SUB%20id%3D%22lingo-sub-781998%22%20slang%3D%22en-US%22%3EUnderstanding%20Datedif%20regarding%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781998%22%20slang%3D%22en-US%22%3E%3CP%3ELook%20at%20column%20C.%20The%20days%20are%20all%2030%20days.%26nbsp%3B%20Why%20are%20they%20all%20the%20same%3F%3C%2FP%3E%3CP%3EI%20agree%20with%20year%20and%20month%20but%20I%20can%20not%20figure%20out%20days.%20%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20example%20is%20Today%20is%20July%2031%2C%202019%20substract%20from%20May%204%2C%202019%20should%20give%200%20years%2C%202%20months%2C%205%20days%20pass!%20Right%20or%20Wrong...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20I%20am%20using%3A%3C%2FP%3E%3CP%3E%3DDATEDIF(B2%2Ctoday()%2C%22y%22)%26amp%3B%22%20years%2C%20%22%3CBR%20%2F%3E%26amp%3BDATEDIF(B2%2Ctoday()%2C%22ym%22)%26amp%3B%22%20months%2C%20%22%3CBR%20%2F%3E%26amp%3Btoday()-DATE(YEAR(today())%2CMONTH(today())%2C1)%26amp%3B%22%20days%22%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20A%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20C%26nbsp%3B%3CBR%20%2F%3Etoday%20date%207%2F31%2F2019%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%208%2F1%2F2017%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%201%20years%2C%2011%20months%2C%2030%20days%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%209%2F1%2F2018%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%200%20years%2C%2010%20months%2C%2030%20days%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201%2F26%2F2019%20%26nbsp%3B%20%26nbsp%3B%200%20years%2C%206%20months%2C%2030%20days%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%208%2F1%2F2018%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%200%20years%2C%2011%20months%2C%2030%20days%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%206%2F28%2F2019%20%26nbsp%3B%20%26nbsp%3B%200%20years%2C%201%20months%2C%2030%20days%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%205%2F4%2F2019%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%200%20years%2C%202%20months%2C%2030%20days%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EPlease%20can%20you%20help%20with%20problems...%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-781998%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-782229%22%20slang%3D%22en-US%22%3ERe%3A%20Understanding%20Datedif%20regarding%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-782229%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385114%22%20target%3D%22_blank%22%3E%40frontdesksm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20created%20a%20video%20tutorial%20about%20the%20DateDiff%20Function%20in%20Excel%3C%2FP%3E%3CP%3EYou%20can%20watch%20it%20here%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9c3DgoTzTU8%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9c3DgoTzTU8%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20Helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-782251%22%20slang%3D%22en-US%22%3ERe%3A%20Understanding%20Datedif%20regarding%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-782251%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385114%22%20target%3D%22_blank%22%3E%40frontdesksm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20your%20formula%20for%20days%20part%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%26amp%3Btoday()-DATE(YEAR(today())%2CMONTH(today())%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eyou%20actually%20subtract%20from%20from%20TODAY()%20first%20day%20of%20the%20current%20month.%20If%20today%20is%2031st%20it%20always%20gives%2031-1%3D30.%3C%2FP%3E%0A%3CP%3EI'd%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DDATEDIF(B2%2CC2%2C%22y%22)%20%26amp%3B%20%22%20years%20%22%20%26amp%3B%20DATEDIF(B2%2CC2%2C%22ym%22)%20%26amp%3B%20%22%20months%20%22%20%26amp%3B%20DATEDIF(B2%2CC2%2C%22md%22)%20%26amp%3B%20%22%20days%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-782252%22%20slang%3D%22en-US%22%3ERe%3A%20Understanding%20Datedif%20regarding%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-782252%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385114%22%20target%3D%22_blank%22%3E%40frontdesksm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20also%20try%20this%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%3DDATEDIF(B2%2CTODAY()%2C%22y%22)%26amp%3B%22%20years%2C%20%22%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3E%26amp%3BDATEDIF(B2%2CTODAY()%2C%22ym%22)%26amp%3B%22%20months%2C%20%22%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3E%26amp%3B(DATEDIF(B2%2CTODAY()%2C%22D%22))-(DATEDIF(B2%2CEDATE(B2%2CDATEDIF(B2%2CTODAY()%2C%22M%22))%2C%22D%22))%26amp%3B%22%20days%22%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125134i6C266798C7E47EBE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-782263%22%20slang%3D%22en-US%22%3ERe%3A%20Understanding%20Datedif%20regarding%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-782263%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385114%22%20target%3D%22_blank%22%3E%40frontdesksm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20the%20way%2C%20if%20to%20add%20some%20cosmetic%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DIF(DATEDIF(B2%2CC2%2C%22y%22)%2CDATEDIF(B2%2CC2%2C%22y%22)%20%26amp%3B%20%22%20years%20%22%2C%20%22%22)%20%26amp%3B%0A%20IF(DATEDIF(B2%2CC2%2C%22y%22)%2BDATEDIF(B2%2CC2%2C%22ym%22)%2CDATEDIF(B2%2CC2%2C%22ym%22)%20%26amp%3B%20%22%20months%20%22%2C%22%22)%20%26amp%3B%0A%20DATEDIF(B2%2CC2%2C%22md%22)%20%26amp%3B%20%22%20days%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-782303%22%20slang%3D%22en-US%22%3ERe%3A%20Understanding%20Datedif%20regarding%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-782303%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385114%22%20target%3D%22_blank%22%3E%40frontdesksm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20DateDif%20function%20does%20not%20show%20a%20tool%20tip%20it's%20an%20old%20function%20that%20Microsoft%20kept%20for%20backward%20compatibility%2C%20although%20it%20works%20just%20fine.%3C%2FP%3E%3CP%3ESo%20you%20can%20either%20copy%20and%20paste%20the%20function%20provided%20by%20the%20Excel%20Guru%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eor%3C%2FP%3E%3CP%3ELearn%20about%20the%20function%20in%20details%20from%20my%20tutorial%20in%20the%20previous%20reply%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784598%22%20slang%3D%22en-US%22%3ERe%3A%20Understanding%20Datedif%20regarding%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784598%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20one%20work!!!!%20Thank%20for%20all%20your%20great%20information.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-785719%22%20slang%3D%22en-US%22%3ERe%3A%20Understanding%20Datedif%20regarding%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785719%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DDATEDIF(C8%2CTODAY()%2C%22y%22)%26amp%3B%22%20years%2C%20%22%3CBR%20%2F%3E%26amp%3BDATEDIF(C8%2CTODAY()%2C%22ym%22)%26amp%3B%22%20months%2C%20%22%3CBR%20%2F%3E%26amp%3BDATEDIF(C8%2CTODAY()%2C%22md%22)%26amp%3B%22%20days%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EIf%20the%20cell%20is%20empty%20what%20would%20I%20add%20to%20the%20formula%20to%20not%20calculate%3F%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EThank%20you%20for%20all%20your%20great%20information.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-785747%22%20slang%3D%22en-US%22%3ERe%3A%20Understanding%20Datedif%20regarding%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785747%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385114%22%20target%3D%22_blank%22%3E%40frontdesksm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20below%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(C8%3D%22%22%2C%22%22%2CDATEDIF(C8%2CTODAY()%2C%22y%22)%26amp%3B%22%20years%2C%20%22%26amp%3BDATEDIF(C8%2CTODAY()%2C%22ym%22)%26amp%3B%22%20months%2C%20%22%26amp%3BDATEDIF(C8%2CTODAY()%2C%22md%22)%26amp%3B%22%20days%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-785913%22%20slang%3D%22en-US%22%3ERe%3A%20Understanding%20Datedif%20regarding%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785913%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%20face%3D%22Calibri%22%3EYea%20again..I%20am%20so%20excited.%26nbsp%3B%20Thank%20YOU!!!!%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786012%22%20slang%3D%22en-US%22%3ERe%3A%20Understanding%20Datedif%20regarding%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786012%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20are%20welcome%26nbsp%3B%20%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385114%22%20target%3D%22_blank%22%3E%40frontdesksm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
frontdesksm
Occasional Contributor

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

@frontdesksm 

 

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

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

@frontdesksm 

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"

 

@frontdesksm 

 

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"

 

clipboard_image_1.png

@frontdesksm 

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"

 

@frontdesksm 

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

Nabil Mourad

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

 

 

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

@frontdesksm 

 

You can use below :

 

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

 

Thanks

Tauqeer

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

@tauqeeracma 

 

You are welcome    @frontdesksm