+ Reply to Thread
Results 1 to 6 of 6

Calculate hours with predefined Working Hours and Weekend Hours

  1. #1
    Registered User
    Join Date
    06-28-2013
    Location
    San Jose, CA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Calculate hours with predefined Working Hours and Weekend Hours

    Hello Excel Forum Community,
    I have looked through threads and not finding quite what I need for this calculation. I thought that the NETWORKDAYS function would help but I see that it just calculates for a certain hours as seen in this thread but my definition is a bit different. My working hours is set starting Monday at 8:00 and ending Friday at 18:00 and weekend hours start Friday 18:00 and go to Monday 8:00. I have attached a sample of what I'm looking for - any help is much appreciated.

    Thank you for taking the time to read. Have a great day!
    Attached Files Attached Files

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

    Re: Calculate hours with predefined Working Hours and Weekend Hours

    Some of your dates are 2012, I assume they should all be 2013......

    If you correct those then this formula in D2 should give the correct result for weekday hours

    =(INT(C2-"8:00")-WEEKDAY(C2-"8:00",3)-INT(B2-"8:00")+WEEKDAY(B2-"8:00",3))*106/7+MIN(106,(WEEKDAY(C2-"8:00",3)+MOD(C2-"8:00",1))*24)-MIN(106,(WEEKDAY(B2-"8:00",3)+MOD(B2-"8:00",1))*24)

    format as number and copy down

    ....then use this formula in E2 to get the remaining (weekend) hours

    =ROUND((C2-B2)*24-D2,9)
    Audere est facere

  3. #3
    Registered User
    Join Date
    06-28-2013
    Location
    San Jose, CA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Calculate hours with predefined Working Hours and Weekend Hours

    Thanks for that formula but there are dates in 2012 and some that span 2012 to 2013. Will that change the formula by much?

  4. #4
    Registered User
    Join Date
    06-28-2013
    Location
    San Jose, CA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Calculate hours with predefined Working Hours and Weekend Hours

    I have played with the formula and it works for 2012 and those dates that span 2012 to 2013. Thank you for your help!

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

    Re: Calculate hours with predefined Working Hours and Weekend Hours

    Yes, my suggested formula should work for any date period.

    My comments about 2012/2013 were related to your specific examples. For the results you expected some of the years were clesrly incorrect

  6. #6
    Registered User
    Join Date
    06-28-2013
    Location
    San Jose, CA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Calculate hours with predefined Working Hours and Weekend Hours

    Thank you - I see that now.

+ 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: 2
    Last Post: 06-14-2013, 10:45 AM
  2. Replies: 7
    Last Post: 03-31-2013, 03:09 PM
  3. Replies: 4
    Last Post: 02-12-2013, 07:01 AM
  4. Replies: 2
    Last Post: 02-11-2013, 02:26 PM
  5. Replies: 4
    Last Post: 11-30-2011, 03:25 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