Display Difference between Two dates Depending on Length of Difference

[jski21]

Good Day Mr. Excel Team,

Is there a way to show the difference between two dates with the result specific to the time frame? Example: if the length is less than 6 months, the result will be in "000 days". If the length of time is greater than 6 months but less than 1 year, the result will display as "0.0 months", and if the length of difference is one year or more, the result will display "0.0 years".

Looking for this in one formula perhaps? Possible?

Thanks,

jski

 

[Saba Sabaratnam]

Hi jski,

you may want to use the following formula.

My spreadsheet layout is given below.

Enter the following formula in C1 and copy it down

==IF((DATEDIF(A1,B1,"m"))<6,DATEDIF(A1,B1,"d"),IF((DATEDIF(A1,B1,"m"))>=12,DATEDIF(A1,B1,"y"),DATEDIF(A1,B1,"m")))


Kind regards

Saba

 

[Saba Sabaratnam]

This is the updated spreadsheet layout.

Kind regards

Saba

 

[jski21]

Hi jski,

you may want to use the following formula.

My spreadsheet layout is given below.


View attachment 31772

Enter the following formula in C1 and copy it down

==IF((DATEDIF(A1,B1,"m"))<6,DATEDIF(A1,B1,"d"),IF((DATEDIF(A1,B1,"m"))>=12,DATEDIF(A1,B1,"y"),DATEDIF(A1,B1,"m")))


Kind regards

Saba

I see...thank you. I could add TEXT Formulas if I wanted to have it say "180 days", or "6 months", or "1 year", yes?

 

[Saba Sabaratnam]

Yes you can.

or you can use the following updated formula.

=IF((DATEDIF(A1,B1,"m"))<6,IF(DATEDIF(A1,B1,"d")=1, DATEDIF(A1,B1,"d")&" day", DATEDIF(A1,B1,"d")&" days"),IF((DATEDIF(A1,B1,"m"))>=12,IF(DATEDIF(A1,B1,"y")=1,DATEDIF(A1,B1,"y")& " year",DATEDIF(A1,B1,"y")& " years"),DATEDIF(A1,B1,"m")&" months"))

The output of the formula is shown below.

Kind regards

Saba

 

[jski21]

Yes you can.

or you can use the following updated formula.

=IF((DATEDIF(A1,B1,"m"))<6,IF(DATEDIF(A1,B1,"d")=1, DATEDIF(A1,B1,"d")&" day", DATEDIF(A1,B1,"d")&" days"),IF((DATEDIF(A1,B1,"m"))>=12,IF(DATEDIF(A1,B1,"y")=1,DATEDIF(A1,B1,"y")& " year",DATEDIF(A1,B1,"y")& " years"),DATEDIF(A1,B1,"m")&" months"))

The output of the formula is shown below.

View attachment 31774


Kind regards

Saba

Got it Saba. Very instructive. Thanks for your time!