Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
because
 
Posts: n/a
Default 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   Report Post  
Bob Phillips
 
Posts: n/a
Default

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   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
convert dates to number of days milk0s Excel Worksheet Functions 2 September 28th 05 01:31 PM
how can I convert numeric number into text firas Excel Discussion (Misc queries) 1 September 21st 05 07:05 PM
Convert arabic number to english text TSK Excel Discussion (Misc queries) 2 July 9th 05 10:24 AM
How can I convert a date into a week number... msbutton27 Excel Worksheet Functions 7 May 25th 05 09:44 PM
Converting number of days to years and months Dave Cobb Excel Worksheet Functions 4 May 24th 05 09:14 PM


All times are GMT +1. The time now is 08:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"