+ Reply to Thread
Results 1 to 11 of 11

issue with converting hours:minutes into 8-hour working day

  1. #1
    Registered User
    Join Date
    06-05-2014
    Location
    Stavanger, Norway
    MS-Off Ver
    2013
    Posts
    5

    issue with converting hours:minutes into 8-hour working day

    Hi,

    I have this spreadsheet to enter my working time.
    I enter all the hours, minutes
    then I have a cell that sum them up (still in hours:min)

    here is an screenshot:
    cells.png

    cell_total_hours.png


    I want to convert that total to a 8-hour working day.

    With the above example, I expect to see: 1 day(s) and 3 hour(s).
    If not possible, then 1.375 day(s)

    I tried many formulas from different websites, but I never get the desired result.

    Thanks for any help,

    Caj.

    Edit: I use excel 2013
    Last edited by cajolino; 06-05-2014 at 08:45 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: issue with converting hours:minutes into 8-hour working day

    A picture (or even 2) is not much use to us - post an example workbook instead.

    You should be aware that times are stored internally by Excel as fractions of a 24-hour day, so 6:00 is actually stored as 0.25, so you will need to add the times and multiply by 3 to get the answer expressed in fractions of an 8-hour day - format the cell as General.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    06-05-2014
    Location
    Stavanger, Norway
    MS-Off Ver
    2013
    Posts
    5

    Re: issue with converting hours:minutes into 8-hour working day

    Quote Originally Posted by Pete_UK View Post
    A picture (or even 2) is not much use to us - post an example workbook instead.

    You should be aware that times are stored internally by Excel as fractions of a 24-hour day, so 6:00 is actually stored as 0.25, so you will need to add the times and multiply by 3 to get the answer expressed in fractions of an 8-hour day - format the cell as General.

    Hope this helps.

    Pete
    Hi,

    I've attached the excel file as requested.
    you will also see all the testings I have done so far, sorry for that noise.

    thx,

    Caj.
    Attached Files Attached Files

  4. #4
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: issue with converting hours:minutes into 8-hour working day

    hey, check out the attachment
    Attached Files Attached Files
    Click just below left if it helps, Boo?ath?

  5. #5
    Forum Contributor
    Join Date
    06-11-2014
    MS-Off Ver
    Office 2003, 2007 & 2010
    Posts
    119

    Re: issue with converting hours:minutes into 8-hour working day

    Assuming your subtotal in hours is in cell B3 for example

    I don't see how 27 hours equates to one 8 hour working day and three hours therefore I assume you want the days split into 24 hours:

    Please Login or Register  to view this content.
    If not, simply replace theoccurences of /24 with /8

  6. #6
    Forum Contributor
    Join Date
    06-11-2014
    MS-Off Ver
    Office 2003, 2007 & 2010
    Posts
    119

    Re: issue with converting hours:minutes into 8-hour working day

    Hi Cajolino, can you confirm if this is solved please?

  7. #7
    Registered User
    Join Date
    06-05-2014
    Location
    Stavanger, Norway
    MS-Off Ver
    2013
    Posts
    5

    Re: issue with converting hours:minutes into 8-hour working day

    Hi Booşathì
    thanks for you reply.
    sorry for the delay, I first didn't understand your spreadsheet. Now I do.

    I think I was not clear with my expectations.

    G36 shows the number of hours, like in my original spreadsheet: 124:00 (e.g. 124 hours). This is fine and should not be changed.
    G38 should show the conversion: "15 days, 4 hours" based on cell G36


    Thanks,

    Caj,

    Notes:
    In G38, I don't need the minutes, I'll always make sure that the total time at the end of the month doesn't have minutes.
    you can forget about cells G37 and G39

  8. #8
    Forum Contributor
    Join Date
    06-11-2014
    MS-Off Ver
    Office 2003, 2007 & 2010
    Posts
    119

    Re: issue with converting hours:minutes into 8-hour working day

    Will the formula I provided not meet this criteria?

  9. #9
    Registered User
    Join Date
    06-05-2014
    Location
    Stavanger, Norway
    MS-Off Ver
    2013
    Posts
    5

    Re: issue with converting hours:minutes into 8-hour working day

    hi AranDG,

    your formula is "correct", there is a just a small hiccup:

    I used your formula as follow (changes are in bold):
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But the result gives this:

    15 day(s) and 3.99999999999999 hour(s)

    if I only take the formula for the minutes:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Then I get a perfect 4

    if I add the text:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I get again: 3.99999999999999 hour(s)

    I also tried to use the CONCATENATE function and I have the same issue.

    Has anyone a solution for this strange behavior?

    Thanks,

    Caj

  10. #10
    Forum Contributor
    Join Date
    06-11-2014
    MS-Off Ver
    Office 2003, 2007 & 2010
    Posts
    119

    Re: issue with converting hours:minutes into 8-hour working day

    Apologies, I'm thinking in terms of VBA here. Right, the reason this is coming up with two different answers is that when the text is applied to the cell as well as the number, the cell assumes the format is text and gives the number as accurate as possible to 15 significant figures, whereas if the formula is emtered without the text, the cell is formatted at a number without decimals (if you increase the number of decimals you will encounter the same issue as with the text).

    To avoid this, you can either use the ROUND or TEXT functions:

    The ROUND function instructs excel to round the number to a certain number of decimal places. The zero at the end of the ROUND function in the example below shows that the number will be rounded to zero decimal places.

    Please Login or Register  to view this content.
    The TEXT function turns the number into text and formats the string as you require. In the example below, the text is formated as "0", therefore an integer.

    Please Login or Register  to view this content.
    Apologies for the inconvenience, I didn't think to take into account the formatting of the result
    Last edited by AranDG; 06-17-2014 at 01:11 PM. Reason: Gorilla Fingers

  11. #11
    Registered User
    Join Date
    06-05-2014
    Location
    Stavanger, Norway
    MS-Off Ver
    2013
    Posts
    5

    Re: issue with converting hours:minutes into 8-hour working day

    hi AranDG,

    Actually, no, if I format the cell and increase the number of decimal, I get: 4.000000
    so it' not a rounding problem. G36 contains 124 hours, so there should not be any rounding issue

    your new function with round() cannot work as it looses the hours altogether
    your new function with text() is almost correct, it was just missing the correct format_text parameter.

    So the correct formula is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Many thanks for your help on this one.

    Problem solved now.

    I attached an updated XLS to show it (2014.xlsx)
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 13
    Last Post: 05-03-2013, 08:42 PM
  2. Replies: 1
    Last Post: 04-13-2013, 05:19 AM
  3. Converting hours and minutes in military time to minutes
    By Argile79 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-13-2010, 02:42 PM
  4. Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..
    By krfarmer in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-25-2010, 05:21 AM
  5. [SOLVED] converting Days Hours & minutes into just minutes in excel
    By Six Sigma Blackbelt in forum Excel General
    Replies: 5
    Last Post: 04-28-2006, 04:45 PM

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