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 DATEDIF will calculate all that for you. Although you don't find it in the Insert Function dialog box, and although the Intellisense list ...

@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