how do I convert a number to number of years, months and days

B

because

In cell a1 I have a number that represents hours worked by an employee. I
need a formula that converts that number into years, months and days.All I
can do is come up with a formula that brings up the result 8.5 years
 
B

Bob Phillips

This loosely works based upon 365 days per year, 30 days per month

=INT(A1/24/365)&" Years, "&INT((A1-INT(A1/24/365)*24*365)/24/30)&" Months,
"&(A1-INT(A1/24/365)*24*365-INT((A1-INT(A1/24/365)*24*365)/24/30)*24*30)/24&
" Days"
 
R

Ron Rosenfeld

In cell a1 I have a number that represents hours worked by an employee. I
need a formula that converts that number into years, months and days.All I
can do is come up with a formula that brings up the result 8.5 years
Click to expand...

How many hours do you consider that the employee works in a year/month/day?

Do you account for overtime?

How many days do you want to assume in a year or a month?

If you are considering that the employee is working 365/24/7 then probably you
should adopt the convention that one year is 365.25 days; and one month is 1/12
of a year.

Then the formula would be

Years: =INT(HrsWrkd/24/365.25)
Months: =INT(MOD(HrsWrkd/24/365.25,1)*12)
Days: =ROUND(MOD((MOD(HrsWrkd/24/365.25,1)*12),1)*365.25/12,1)

But you may have something else in mind.
--ron
 
Top