Nested IF formula question

[Radrays]

I am currently using the formula below to calculate difference in dates in
Excel 2003 where C4 = start date and D4 = completion date. How can I
suppress any "0" in the responses. For example if the result is only 21 days
then I do not want to show 0 years , 0 months. I will always want to show
days.

=DATEDIF(C4,D4,"y")&" years, "& DATEDIF(C4,D4,"ym")&" months, "&
DATEDIF(C4,D4,"md")&" days"

Thank you for your time.

 

[Bob Phillips]

=IF(DATEDIF(C4,D4,"y")=0,"",DATEDIF(C4,D4,"y")&" years, ")
&IF(DATEDIF(C4,D4,"ym")=0,"",DATEDIF(C4,D4,"ym")&" months, ")
&IF(DATEDIF(C4,D4,"md")=0,"",DATEDIF(C4,D4,"md")&" days")

 

[joeu2004]

How can I suppress any "0" in the responses. For example if
the result is only 21 days then I do not want to show 0 years,
0 months. I will always want to show days.

IMHO, it would be easier if you put the individual DATEDIF expressions
in cells (which you can hide). Then you could write:

=if(Y1,Y1&" years, ","") & if(Y2,Y2&" months, ","") & datedif
(C4,D4,"md")&" days"

But if you insist on a single cell for everything, replace Y1 with
DATEDIF(C4,D4,"y") and Y2 with DATEDIF(C4,D4,"ym") in all places.

HTH.


----- original posting -----

 

[Radrays]

Thanks Bob - worked perfect! Appreciate your time.