Convert days to months and years

Hi Adam,

The largest unit in the duration family is weeks because that is the largest unit which has a fixed relationship with the number of days.

Barring 'leap seconds', there are always precisely 24 hours, or 1440 minutes or 86,400 seconds in one day.

One Week always contains exactly seven days.

But one Month can contain 28, 29, 30 or 31 Days, and one Year can contain 365 or 366 Days.

If you convert the 1033 days duration to a number (1033) representing that number of days, you can then divide that number by an arbitrary number (such as 365 or 365.25) to determine the number of years (including the fraction of a year 'left over'. If you want to, you can also extract the number of days in the left over fraction of a year.

Formulas used in the example:

C2: DUR2DAYS(B2)÷A2

D2: QUOTIENT(DUR2DAYS(B2),A2)

E2: MOD(DUR2DAYS(B2),A2)

Each formula has been filled down to row 5.

Cells in column B have been formatted to Duration, with units set to show only days, and the style set to 0d.

Regards,

Barry

Adam Berkey wrote:

how to get a cell to show how many years a duration cell showing 1033d would be?

Here's one way, using the YEARFRAC function:

=YEARFRAC(TODAY(),TODAY()+A2,1)

More on YEARFRAC here.

If your 1003-day duration comes from subtracting two dates (rather than you entering the duration manually) then you could include the two dates in the YEARFRAC function directly, rather than resorting to using TODAY to "create" the two dates in my example.

=YEARFRAC(start-date, end-date, days-basis)

SG

You can try something like this:

The formula in C2 is:

=INT(DATEDIF(TODAY(),TODAY()+D2,"M")÷12)&"Y "&MOD(DATEDIF(TODAY(),TODAY()+D2,"M"),12)&"M"

Adam Berkey wrote:

When I try to simply divide the days by 365, I do not get the result in years, but rather in weeks, days, hours, etc...

Just thought of another way to address your question that may be useful, assuming you want the result to be a number that you may want to use in further calculations. There is a family of "DUR2" functions in Numbers that convert from duration to decimal. So if you want a 365-day year basis, you could do this:

=DUR2DAYS(A3)/365

The result differs from the YEARFRAC approach because of the "days-basis". YEARFRAC is often used in bond math, where a different number of days per year is assumed for different types of debt instruments, hence that third parameter.

SG