+ Reply to Thread
Results 1 to 16 of 16

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

  1. #1
    Registered User
    Join Date
    02-25-2010
    Location
    Converse, texas
    MS-Off Ver
    Excel 2002
    Posts
    8

    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
    Last edited by krfarmer; 02-25-2010 at 04:04 AM.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    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. #3
    Registered User
    Join Date
    02-25-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    1

    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"
    Sachin Acharya

  4. #4
    Registered User
    Join Date
    02-25-2010
    Location
    Converse, texas
    MS-Off Ver
    Excel 2002
    Posts
    8

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

    Quote Originally Posted by sachin.acharya View Post
    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. #5
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    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. #6
    Registered User
    Join Date
    02-25-2010
    Location
    Converse, texas
    MS-Off Ver
    Excel 2002
    Posts
    8

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

    Quote Originally Posted by arthurbr View Post
    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. #7
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    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. #8
    Registered User
    Join Date
    02-25-2010
    Location
    Converse, texas
    MS-Off Ver
    Excel 2002
    Posts
    8

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

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

    G2 is just General format. Whole number.
    Last edited by krfarmer; 02-25-2010 at 04:08 AM.

  9. #9
    Registered User
    Join Date
    02-25-2010
    Location
    Converse, texas
    MS-Off Ver
    Excel 2002
    Posts
    8

    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. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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. #11
    Registered User
    Join Date
    02-25-2010
    Location
    Converse, texas
    MS-Off Ver
    Excel 2002
    Posts
    8

    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:
    Attached Files Attached Files

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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. #13
    Registered User
    Join Date
    02-25-2010
    Location
    Converse, texas
    MS-Off Ver
    Excel 2002
    Posts
    8

    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!
    Last edited by krfarmer; 02-25-2010 at 04:52 AM.

  14. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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. #15
    Registered User
    Join Date
    02-25-2010
    Location
    Converse, texas
    MS-Off Ver
    Excel 2002
    Posts
    8

    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.

    Thanks again for your patience!

  16. #16
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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...
    Last edited by DonkeyOte; 02-25-2010 at 05:23 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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