# Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

1. ## Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

Hello all,

Im trying to create a spreadsheet to track how many hours have been used by an employee. Currently the employee starts with 480 hours and as they use these hours, I am displaying the remainder in days, hours and minutes. This all works fine until I get to 255 hours and then the remaining hours seem to invert, going back to the beginning. For example, if I input that the person has used 256 hours, the end result shows "1 Day, 248 Hours, 0 Minutes". Additionally, if I test the spreadsheet by saying that the person used all 480 hours (60 days), the "Days Used" should be 60, but instead it shows 29 days 248 hours 0 minutes.

This is the formula I am currently using:

=DAY(G2/8)&" days "&(G2/8-DAY(G2/8))*8&" hours "&MINUTE(G2)&" minutes"

Im sure its something simple in my formula, but Im extremely rusty here...

Any help is greatly appreciated!

K

2. ## Re: Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

Hi and welcome to the board

Which format do you use to enter the used time.
Maybe adding a sample sheet would help ( for dates and times formats are not always what they seem)

3. ## Re: Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

The DAY function can take only values from 1 to 31. Use this instead:

=QUOTIENT(G3,8)&" days "&((G3/8)-QUOTIENT(G3,8))*8&" hours "&MINUTE(G3)&" minutes"

4. ## Re: Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

Originally Posted by sachin.acharya
The DAY function can take only values from 1 to 31. Use this instead:

=QUOTIENT(G3,8)&" days "&((G3/8)-QUOTIENT(G3,8))*8&" hours "&MINUTE(G3)&" minutes"
Hello Sachin! This function works fine to get past the 31 day problem but the minutes do not show up ?? Almost there!

Thank you!

EDIT: This works fine...Im half asleep over here. Thank you very much!

5. ## Re: Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

That's why I asked to post the format you use...

6. ## Re: Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

Originally Posted by arthurbr
That's why I asked to post the format you use...
Sorry about that. The hours remaining is simply "=480-G2 (with G2 being the cell referenced in the formula.

7. ## Re: Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

Then G2 is a whole number, or formatted as hh:mm or hh,mm ?

8. ## Re: Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

Originally Posted by arthurbr
Then G2 is a whole number, or formatted as hh:mm or hh,mm ?

G2 is just General format. Whole number.

9. ## Re: Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

Im getting an odd result with the formula. If for example, I enter .26 for twenty six minutes used, the result I get is 0 days 0.26 hours 14 minutes. ??

10. ## Re: Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

Using .26 for minutes where one hour equates to 1 is obviously open to error given time does not work on base 100 (base 60).

Post a sample file - this could be wrapped up in one post I suspect if we could see what you're using.

11. ## Re: Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

Thanks for your help. Here is the file Im working on:

12. ## Re: Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

If as you say the Max Hours allocated is 480 such that the total days remaining can never exceed 20 you could just use a Custom Format (ie you never exceed 31 days).

However, you need to think about how you intend to record partial hours - given you're using decimal hours you should really store partial hours in decimal form, ie 30 mins = 0.5

If you do that then you can leave H2 as is, if not and you opt to record 30 mins as 0.3 then you will need to adjust H2 accordingly, eg:

H2: =480-INT(G2)-(MOD(G2,1)/0.6)

or if you have Analysis ToolPak installed - =480-DOLLARDE(G2,60)

With the above corrected you can adjust I2 such that:

I2: =H2/24

and apply a Custom Format to I2 of: d" days" h "hours" m "minutes"

13. ## Re: Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

DonkeyOte, the 480 hours can be consumed in 15 minute increments. Basically, I would need the formula to display how many days, hours and minutes have been used. I would prefer to keep it decimal (e.g. .25 = 15 minutes) If the user enters .25, it should display 15 minutes. I apologize for not being more clear in this problem. Thank you for your assistance!

14. ## Re: Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

If you opt not to store time as time then you should obviously use Decimal so use of 0.25 would be preferred for G2 etc...

If you do that you should find the formula and format suggestions for I2 (prior post) give you what you want (leaving H2 as was)

The underlying value in I2 will be the actual time value but the cell will display the text string requested by virtue of the Format applied to it.
(storing the time value in I2 may prove valuable latterly)

15. ## Re: Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

DonkeyOte, thanks for your patience...Im not very good with Excel but got tasked with this chore :-(

If I leave everything as is, I get the return I want when I use the Decimal (.25 for 15 minutes and we work in 15 minute increments so this is fine). I realized that listing 'minutes' is redundant when we track in 15 minute increments so that can be represented by the 'hours' numbers. My formula currently looks like this:

=QUOTIENT(G2,8)&" days "&((G2/8)-QUOTIENT(G2,8))*8&" hours "

Do you see anything wrong with this? Its giving me my hours and minutes used so I think my problem is solved. My main problem was getting past the 31 day limit.

16. ## Re: Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

I'm not quite sure what extra info you need.

Using your prior upload enter the formula I provided into I2, namely:

=H2/24

Now apply a Custom Format to I2 (via CTRL + 1) of: d" days" h "hours" m "minutes"

This will generate the requisite string in I2 but the underlying value remains the time serial of H2 decimal form
(ie if H2 = 430 the underling value in I2 is 430:00 or 17.91667 etc in decimal form but will appear/print as "17 days 22 hours 0 minutes")

Given the days can not exceed 31 I see no reason for generating the above with a String based function.

480 hours is 20 days - I'm not sure at this point where the concern is with regard to 31 days...

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1