Formula related

[NathanRS]

Hi,

I have a requirement. I have for e.g. 4591 days. I want
a formula to convert this into that many years, the
balance in months and the least balance as days.

Something like 14 years 3 months 25 days as its
interpretation.

I hope I am able to explain in clear that what I wanted.

Can anybody help me on this please.

Your early reply would be of helpful to me.


Regards,
Nathan

 

[Dan E]

Nathan,

This works, but is only an approximation (ie 365 days in a year, 30 days in
a month)

=QUOTIENT(A1,365) & " years, " & QUOTIENT(MOD(A1,365),30) & " months and " &
QUOTIENT(MOD(MOD(A1,365),30),1) & " days"

Where A1 is the days

Dan E

 

[Ron Rosenfeld]

Hi,

I have a requirement. I have for e.g. 4591 days. I want
a formula to convert this into that many years, the
balance in months and the least balance as days.

Something like 14 years 3 months 25 days as its
interpretation.

I hope I am able to explain in clear that what I wanted.

Can anybody help me on this please.

Your early reply would be of helpful to me.


Regards,
Nathan

You need to be a bit more specific.

You could display 4591 in that format by formatting the cell:

yy" yrs" m" months " d" days"

That would result in 12 yrs 7 months 26 days

However, since both years and months can vary in length, you need to be a bit
more specific as to exactly what you want.

You could display the results in separate columns or rows:

Years = INT(A1/365) = 12
Months = INT((A1-INT(A1/365)*365)/30) = 7
Days = =A1-INT(A1/365)*365-INT((A1-INT(A1/365)*365)/30)*30 = 1

But the above presupposes that every year has 365 days and every month has 30
days, which it does not. That formula will give a different result (for the
number of days).

So what exactly do you require?


--ron

 

[Norman Harker]

Hi Nathan!

As Ron has explained you're dealing with two inexactly defined
variables of years and months. You should also have noted that you're
going to get some unusual results.

If you want precision, then your best bet will be to go back to the
base data and do your calculation from the earliest date to the latest
date using years and days OR years weeks and days OR years and
fractions of a year.

All three options will require formulas which one way or another all
come from Chip Pearson's site:

http://www.cpearson.com/excel/topic.htm

With A1 containing earliest date and B1 the latest date:

Years and Days:
=DATEDIF(A1,B1,"y") & " y " &
B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)) & " d"

Returns (e.g.): 55 y 344 d



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



Years weeks and days:

=DATEDIF(A1,B1,"y") & " y " &
INT((B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))/7) & " w
" & MOD((B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1))),7) &
" d"

Returns (e.g.): 55 y 49 w 1 d



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



Years and fractions of a year:

=DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY
(A1)))

/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEAR(A1)+
DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))

Returns (e.g.): 55.94246575



(Note: YEARFRAC produces errors where dates are 1 or more years apart)

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



All of the above are based upon an algorithm that states that a year
is completed on the anniversary of the earliest date and the residual
calculations relate to the number of days since that anniversary date.
As a result we avoid having to use an average number of days in a
year. With the fraction of a year formula the fraction is calculated
by dividing the number of days since the last anniversary by the
number of days from the last anniversary until the next anniversary.

You can substitute TODAY() B1 in the examples. If you use NOW() you'll
get the same result because the DATEDIF function truncates the time
element in the two dates.

For details of the "drunken cousin" of the Excel function family,
DATEDIF, see:

Chip Pearson:
http://www.cpearson.com/excel/datedif.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Wednesday 16th July: Argentina (Independence
Day); Brazil (Sao Paulo State Civil Holiday); Isle of Man (Senior Race
Day); Morocco (King Hassan II's Birthday); Palau (Constitution Day).
Celebration: Baha'i (Martyrdom of the Bab).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.