Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to calculate end date using order date and lead time variab | Excel Worksheet Functions | |||
Tricky Date calculation: How to calculate a future date | Excel Discussion (Misc queries) | |||
Auto calculate for date + days forward to yield new date | Excel Worksheet Functions | |||
formula to calculate age using birth date and current date | Excel Worksheet Functions | |||
formula to calculate future date from date in cell plus days | Excel Worksheet Functions |