I had a document full of employment start dates in column “A” and end dates in column “B”. Wanted column “C” to calculate length of time in the following format: “1 Year, 2 Months, 3 Days”.
I didn’t find a function for it in Excel (I even searched in “Help”).
So, I did it the hard way:
=CONCATENATE(IF(YEAR(B2)-YEAR(A2)-IF(OR(MONTH(B2)<MONTH(A2),AND(MONTH(B2)=MONTH(A2),
DAY(B2)<DAY(A2))),1,0)=0,””,CONCATENATE(YEAR(B2)-YEAR(A2)-IF(OR(MONTH(B2)<MONTH(A2),AND(MONTH(B2)=MONTH(A2),
DAY(B2)<DAY(A2))),1,0),IF(YEAR(B2)-YEAR(A2)-IF(OR(MONTH(B2)<MONTH(A2),AND(MONTH(B2)=MONTH(A2),
DAY(B2)<DAY(A2))),1,0)=1,” Year “, ” Years “))),IF(MONTH(B2)-MONTH(A2)+IF(AND(MONTH(B2)<=MONTH(A2),DAY(B2)<DAY(A2)),11,IF(AND(MONTH(B2)<MONTH(A2),DAY(B2)>=DAY(A2)),12,IF(AND(MONTH(B2)>MONTH(A2),DAY(B2)<DAY(A2)),-1)))=0,””,CONCATENATE(MONTH(B2)-MONTH(A2)+IF(AND(MONTH(B2)<=MONTH(A2),DAY(B2)<DAY(A2)),11,IF(AND(MONTH(B2)<MONTH(A2),DAY(B2)>=DAY(A2)),12,IF(AND(MONTH(B2)>MONTH(A2),DAY(B2)<DAY(A2)),-1))),IF(MONTH(B2)-MONTH(A2)+IF(AND(MONTH(B2)<=MONTH(A2),DAY(B2)<DAY(A2)),11,IF(AND(MONTH(B2)<MONTH(A2),DAY(B2)>=DAY(A2)),12,IF(AND(MONTH(B2)>MONTH(A2),DAY(B2)<DAY(A2)),-1)))=1,” Month “, ” Months “))),IF(B2-DATE(YEAR(B2),MONTH(B2)-IF(DAY(B2)<DAY(A2),1,0),DAY(A2))=0,””,CONCATENATE(B2-DATE(YEAR(B2),MONTH(B2)-IF(DAY(B2)<DAY(A2),1,0),DAY(A2)),IF(B2-DATE(YEAR(B2),MONTH(B2)-IF(DAY(B2)<DAY(A2),1,0),DAY(A2))=1,” Day”,” Days”))))
That worked, but took too long to figure out. Later a coworker mentioned a function called “datedif”. A Bing search revealed:
“This function is available in all versions of Excel since at least version 5/95, but is documented in the help file only for Excel 2000. For some reason, Microsoft has decided not to document this function in any other versions. DATEDIF is treated as the drunk cousin of the Formula family. Excel knows it lives a happy and useful life, but will not speak of it in polite conversation.” – http://www.cpearson.com/excel/datedif.aspx
So, I could have just done it the easy way:
=CONCATENATE(IF(DATEDIF(A2,B2,”y”)=0,””,CONCATENATE(DATEDIF(A2,B2,”y”),IF(DATEDIF(A2,B2,”y”)=1,” Year “,” Years “))),IF(DATEDIF(A2,B2,”ym”)=0,””,CONCATENATE(DATEDIF(A2,B2,”ym”),IF(DATEDIF(A2,B2,”ym”)=1,” Month “,” Months “))),IF(DATEDIF(A2,B2,”md”)=0,””,CONCATENATE(DATEDIF(A2,B2,”md”),IF(DATEDIF(A2,B2,”md”)=1,” Day”,” Days”))))
Example
Start: 3/27/2007
End: 7/27/2008
Result: 1 Year 4 Months
It would have been (was) a lot simpler. The formula is still longer than necessary, but I like for it to only show values that are not 0 and I also like when the value is 1 that it uses the correct suffix.
Here is the formula to use if you want to see all 3 values even if the value is 0:
=CONCATENATE(CONCATENATE(DATEDIF(A2,B2,”y”),IF(DATEDIF(A2,B2,”y”)=1,” Year “,” Years “)),CONCATENATE(DATEDIF(A2,B2,”ym”),IF(DATEDIF(A2,B2,”ym”)=1,” Month “,” Months “)),CONCATENATE(DATEDIF(A2,B2,”md”),IF(DATEDIF(A2,B2,”md”)=1,” Day”,” Days”)))
Example
Start: 3/27/2007
End: 7/27/2008
Result: 1 Year 4 Months 0 Days