Calculating Length of time in Microsoft Excel

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

Advertisements
This entry was posted in Computers and Internet. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s