+ Reply to Thread
Results 1 to 13 of 13

Working hours attempt URGENT

  1. #1
    Registered User
    Join Date
    12-15-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Working hours attempt URGENT

    In a bit of a pickle, I thought id solve this by today and am running myself in circles...

    I have been working on a spreadsheet that requires me to calculate the number of working hours (9-5) spent on a number of tasks that can either be resolved in a day or span multiple days/weeks/months. Oddly, my attempt is correct in some instances (marked green) but incorrect in other (marked red).

    I would be eternally grateful if someone could dig me out of a big hole and provide the correct formula.

    Many thanks

    Max
    Attached Files Attached Files

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Working hours attempt URGENT

    Hello Max,

    I think your formula is fine as long as all the time/date entries are within working hours - I2 seems to have the wrong year, though (2011 rather than 2013), if you fix that I think everything will work OK
    Audere est facere

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,456

    Re: Working hours attempt URGENT

    Hi and welcome to the forum

    Did you notice that thet date in I2 is 2011, not 2013?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    12-15-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Working hours attempt URGENT

    Thanks for highlighting the date, that was an error.

    I have however corrected and it still yields the wrong hour count.

    I have attached an updated spreadsheet to refer to, which also quotes what the hours should be.

    Help appreciated.

    Max
    Attached Files Attached Files

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,351

    Re: Working hours attempt URGENT

    Just custom format J2 and O2 as [hh]:mm to get the correct values. (hh:mm won't do when over 24 hours)

    It seems that the values you indicated as "should be" are not correct.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,664

    Re: Working hours attempt URGENT

    Hours between 11/28/2013 11:00 & 11/27/2013 11:21 is 9:21 OR 8:21?

  7. #7
    Registered User
    Join Date
    12-15-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Working hours attempt URGENT

    Quote Originally Posted by Pepe Le Mokko View Post
    Just custom format J2 and O2 as [hh]:mm to get the correct values. (hh:mm won't do when over 24 hours)

    It seems that the values you indicated as "should be" are not correct.
    How do I custom format as per above?

    You are correct 9:21 should have read 8:21

  8. #8
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,351

    Re: Working hours attempt URGENT

    Right click the cell - Format Cells - Number tab - Custom - select the needed format or create it if it is not there ( it generally is)

  9. #9
    Registered User
    Join Date
    12-15-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Working hours attempt URGENT

    Many thanks all

  10. #10
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,351

    Re: Working hours attempt URGENT

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  11. #11
    Registered User
    Join Date
    12-15-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Working hours attempt URGENT

    Quick supplementary question,

    Can someone explain to me what the MOD function does in reference to the formula in M2 of the spreadsheet in the previously attached workbook?

    Many thanks

    Max

  12. #12
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Working hours attempt URGENT

    MOD is used in this case to extract the time only from a combined time and date, so if K2 = 29/4/2014 13:17 then you can extract the time only (13:17) by using

    =MOD(K2,1)

  13. #13
    Registered User
    Join Date
    12-15-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Working hours attempt URGENT

    Many thanks

+ 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: 3
    Last Post: 10-10-2013, 10:15 PM
  2. Elapsed working hours, without counting weekends or non-working hours
    By ebkiwi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-16-2013, 04:18 PM
  3. [SOLVED] Calculate hours with predefined Working Hours and Weekend Hours
    By garciapliz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-05-2013, 11:17 AM
  4. Replies: 2
    Last Post: 06-14-2013, 10:45 AM
  5. Urgent help reqd. in calculating working hours
    By prabodhkgupta in forum Excel General
    Replies: 0
    Last Post: 10-10-2006, 03:11 AM

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