Supressing 0's in a Datedif formula...

[JennOlsen]

I have a formula that I use to get the length of employment for someone, however, I would really love it there was a way to supress parts if the results is a 0.

The formula I am using is:
=DATEDIF([@[Hire Date]],[@[Term Date]],"Y")&" year(s) "&DATEDIF([@[Hire Date]],[@[Term Date]],"ym")&" month(s) "&DATEDIF([@[Hire Date]],[@[Term Date]],"md")&" day(s) "

I the year, month, or days is 0, I don't want to see it. So if someone has been employed for only 90 days, I don't want to see 0 years, 0 months, 90 days. I just want to see 90 days.

Help?

[hoyasaxa215]

Is "=DATEIF" a UDF? I've never heard of that function. Could you possibly upload a sample file?

[JennOlsen]

Here's the sample. It's in the Raw Data tab.

[FDibbins]

=DATEDIF() needs 2 (real) dates to work with, then an argument...
=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",DATEDIF(A1,B1,"ym")&" months ")&DATEDIF(A1,B1,"md")&" days"
=DATEDIF(A1,B1,"y")...will give years
=DATEDIF(A1,B1,"m")...will give months
=DATEDIF(A1,B1,"d")...will give days

[JennOlsen]

=DATEDIF() needs 2 (real) dates to work with, then an argument...
=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",DATEDIF(A1,B1,"ym")&" months ")&DATEDIF(A1,B1,"md")&" days"
=DATEDIF(A1,B1,"y")...will give years
=DATEDIF(A1,B1,"m")...will give months
=DATEDIF(A1,B1,"d")...will give days

This worked perfect.

Thanks!

[martindwilson]

id have thought there would be at least 2 months in 90 days

[JennOlsen]

id have thought there would be at least 2 months in 90 days

Well, yes, there is of course. It was more just an example. No need to pick at my momentary brain lapse, lol.

[martindwilson]

@ hoyasaxa215
http://www.cpearson.com/excel/datedif.aspx

[JennOlsen]

@ hoyasaxa215
http://www.cpearson.com/excel/datedif.aspx

Very informative. Thank you.

[hoyasaxa215]

Interesting. Thanks.