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

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"

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.

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.

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"

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!

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)

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...