Calculate age with an end date
 

I am currently using the formula =DATEDIF(J5,TODAY(),"y")&" years
"&DATEDIF(J5,TODAY(),"ym")&" months "&DATEDIF(J5,TODAY(),"md")&" days" to
calculate the length of time I have a cigar in a humidor. The start date is
in column J and the age displays in column K. What do I need to add so that
when I put an end date in column L (the date the cigar is smoked), the age
displays just the length of time between the dates?

Calculate age with an end date
 

Replace each instance of TODAY() with:

IF(L5="",TODAY(),L5)

--
Biff
Microsoft Excel MVP


"punkrawk_99" wrote in message
...
I am currently using the formula =DATEDIF(J5,TODAY(),"y")&" years
"&DATEDIF(J5,TODAY(),"ym")&" months "&DATEDIF(J5,TODAY(),"md")&" days" to
calculate the length of time I have a cigar in a humidor. The start date
is
in column J and the age displays in column K. What do I need to add so
that
when I put an end date in column L (the date the cigar is smoked), the age
displays just the length of time between the dates?

Calculate age with an end date
 

Punkrawk_99,
My solution is as follows:
First format "L" column as Date and time (i.e. 5/6/08 13:00)
Edit your formula as follows; "=IF(L5="",DATEDIF(J5,TODAY(),"y")&" years
"&DATEDIF(J5,TODAY(),"ym")&" months
"&DATEDIF(J5,TODAY(),"md")&"days",DATEDIF(J5,L5,"y ")&" years
"&DATEDIF(J5,L5,"ym")&" months "&DATEDIF(J5,L5,"md")&"days")"

What this does is look at the cell in column L, if there is nothing in it.
It calculates how long the cigar has been in the humidor.
If the entry in "L5" is a date, it calculates from that date.

P.S.
It is a little more involved than T. Valko, but it also works.

"punkrawk_99" wrote:

I am currently using the formula =DATEDIF(J5,TODAY(),"y")&" years
"&DATEDIF(J5,TODAY(),"ym")&" months "&DATEDIF(J5,TODAY(),"md")&" days" to
calculate the length of time I have a cigar in a humidor. The start date is
in column J and the age displays in column K. What do I need to add so that
when I put an end date in column L (the date the cigar is smoked), the age
displays just the length of time between the dates?

Calculate age with an end date
 

Thanks. I got it working.

"FloMM2" wrote:

Punkrawk_99,
My solution is as follows:
First format "L" column as Date and time (i.e. 5/6/08 13:00)
Edit your formula as follows; "=IF(L5="",DATEDIF(J5,TODAY(),"y")&" years
"&DATEDIF(J5,TODAY(),"ym")&" months
"&DATEDIF(J5,TODAY(),"md")&"days",DATEDIF(J5,L5,"y ")&" years
"&DATEDIF(J5,L5,"ym")&" months "&DATEDIF(J5,L5,"md")&"days")"

What this does is look at the cell in column L, if there is nothing in it.
It calculates how long the cigar has been in the humidor.
If the entry in "L5" is a date, it calculates from that date.

P.S.
It is a little more involved than T. Valko, but it also works.

"punkrawk_99" wrote:

I am currently using the formula =DATEDIF(J5,TODAY(),"y")&" years
"&DATEDIF(J5,TODAY(),"ym")&" months "&DATEDIF(J5,TODAY(),"md")&" days" to
calculate the length of time I have a cigar in a humidor. The start date is
in column J and the age displays in column K. What do I need to add so that
when I put an end date in column L (the date the cigar is smoked), the age
displays just the length of time between the dates?