how do I convert a number to number of years, months and days
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 |
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" -- HTH Bob Phillips "because" wrote in message ... 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 |
On Wed, 12 Oct 2005 08:15:02 -0700, "because"
wrote: 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 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 |
All times are GMT +1. The time now is 10:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com