Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert dates to number of days | Excel Worksheet Functions | |||
how can I convert numeric number into text | Excel Discussion (Misc queries) | |||
Convert arabic number to english text | Excel Discussion (Misc queries) | |||
How can I convert a date into a week number... | Excel Worksheet Functions | |||
Converting number of days to years and months | Excel Worksheet Functions |