# 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  Register To Reply

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)  Register To Reply

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"  Register To Reply

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!  Register To Reply

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...  Register To Reply

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.  Register To Reply

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 ?  Register To Reply

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.  Register To Reply

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. ??  Register To Reply

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.  Register To Reply

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:  Register To Reply

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"  Register To Reply

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!  Register To Reply

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)  Register To Reply

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.  Register To Reply

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...  Register To Reply