converting dates

[Guest]

Hi All

I have a couple of excel problems to do with dates (Excel XP/2002).

I have a formula that displays one date minus another and displays the answer as the number of days. Firstly, is it possible to convert the data to display as year and days (375 displays as 1 year and 10 days) as it displays as a decimal point when I divide by 365.

Secondly does anyone know a formula that sorts out leap years (see above).

Any help, suggestions or tutorial links greatly appreciated

Rexmann

 

[Bob Phillips]

As long as you assume 1 year is always 365 days

=INT(A1/365)&" year(s) "&MOD(A1,365)&" days"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi All

I have a couple of excel problems to do with dates (Excel XP/2002).

I have a formula that displays one date minus another and displays the

answer as the number of days. Firstly, is it possible to convert the data to
display as year and days (375 displays as 1 year and 10 days) as it displays
as a decimal point when I divide by 365.

 

[Norman Harker]

Hi Rexmann!

For a return of years and days:

=DATEDIF(A1,B1,"y") & " y " &
B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)) & " d"
returns 55 y 344 d

(Note: DATEDIF approach using "yd" produces errors)

You'll find that this handles Leap Years OK.

 

[Guest]

Thank you both for the formulas - just what I wanted

cheers Rexmann

 

[Norman Harker]

Hi Rexmann!

Always pleased to help. For further details on this topic, you'll find
Google Searching on "Age" will bring up a whole range of formulas for
different requirements. But watch out! Some of the age stuff in Excel
is very buggy and / or uses algorithms that are not thought
appropriate by many of us mere mortals.