Nested IF formula question
 

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.
--
Radrays

Nested IF formula question
 

=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")


--
__________________________________
HTH

Bob

"Radrays" wrote in message
...
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.
--
Radrays

Nested IF formula question
 

On Feb 24, 2:59 pm, Radrays wrote:
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 -----

On Feb 24, 2:59*pm, Radrays wrote:
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.
--
Radrays

Nested IF formula question
 

Thanks Bob - worked perfect! Appreciate your time.

"Bob Phillips" wrote:

=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")


--
__________________________________
HTH

Bob

"Radrays" wrote in message
...
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.
--
Radrays