CONVERTING TIME (DAYS) TO ONTHS,YEARS, ETC.

[rmriba]

Hello, Gurus:

I have multiple licenses (potentially 50+), each one with an expiration
date. I have a sheet with expiration date column and another column
with the difference between expiration date and current date. The
display shows the result in days.

I would like it to show it in years-months-days format. Excel has a
similar function for converting to hrs, sec, etc., but no such beauty
(apparently) for this case. Any help will be appreciated.

[Dave Peterson]

Maybe you could use =datedif()

You can find lots of info at Chip Pearson's site:
http://www.cpearson.com/excel/datedif.htm

(=datedif() was only documented in xl2k's help.)

"rmriba" wrote:
>
> Hello, Gurus:
>
> I have multiple licenses (potentially 50+), each one with an expiration
> date. I have a sheet with expiration date column and another column
> with the difference between expiration date and current date. The
> display shows the result in days.
>
> I would like it to show it in years-months-days format. Excel has a
> similar function for converting to hrs, sec, etc., but no such beauty
> (apparently) for this case. Any help will be appreciated.

--

Dave Peterson

[Adam Molinaro]

This is kind of clunky, so there might be simpler/slicker ways of doing it,
but you could use the TEXT function. You could do the subtraction of dates
to get your years, months, and days separately, then putting them all
together (with a separating character; I used a dash).

Let's suppose your first date is in D9 (9/26/2005), and your second date is
in D15 (5/9/2003). Enter the following formula:

=TEXT(D9,"yyyy")-TEXT(D15,"yyyy")&"-"&TEXT(D9,"mm")-TEXT(D15,"mm")&"-"&TEXT(D9,"dd")-TEXT(D15,"dd"). Your output would be:
2-4-17 (years - months - days).

The & concatenates/combines the formulae, and the "-" puts in the separators.

You could also put them in separate cells. For example,
=TEXT(D9,"YYYY")-TEXT(D15,"YYYY) would yield simply '2.'

HTH.

Adam.

"Dave Peterson" wrote:

> Maybe you could use =datedif()
>
> You can find lots of info at Chip Pearson's site:
> http://www.cpearson.com/excel/datedif.htm
>
> (=datedif() was only documented in xl2k's help.)
>
> "rmriba" wrote:
> >
> > Hello, Gurus:
> >
> > I have multiple licenses (potentially 50+), each one with an expiration
> > date. I have a sheet with expiration date column and another column
> > with the difference between expiration date and current date. The
> > display shows the result in days.
> >
> > I would like it to show it in years-months-days format. Excel has a
> > similar function for converting to hrs, sec, etc., but no such beauty
> > (apparently) for this case. Any help will be appreciated.
>
> --
>
> Dave Peterson
>

[rmriba]

Much appreciated, both of you. It worked like a charm.

rmriba wrote:
> Hello, Gurus:
>
> I have multiple licenses (potentially 50+), each one with an expiration
> date. I have a sheet with expiration date column and another column
> with the difference between expiration date and current date. The
> display shows the result in days.
>
> I would like it to show it in years-months-days format. Excel has a
> similar function for converting to hrs, sec, etc., but no such beauty
> (apparently) for this case. Any help will be appreciated.
>