+ Reply to Thread
Results 1 to 5 of 5

Calculate hours between 2 dates - exclude weekend hours

  1. #1
    Registered User
    Join Date
    05-08-2015
    Location
    Atlanta, GA
    MS-Off Ver
    2013
    Posts
    5

    Calculate hours between 2 dates - exclude weekend hours

    I know this is commonly asked question, and I've done a lot of reading and trial/error.

    I need to calculate the number of hours elapsed between 2 date/time stamps, excluding weekend hours.

    Using =NETWORKDAYS(date1,date2)-1-MOD(date1,1)+MOD(date2,1) works pretty well, but only when both of the date/time stamps are on weekdays. In essence it's just subtracting 48 hours for the weekend.

    However, when one of the date/time stamps is on a weekend, it doesn't work, and this is my problem.

    For example: date1 = 5/17/2015 21:00
    date2 = 5/18/2015 11:00

    14 total hours have elapsed, but I want my formula to display 11 hours, excluding hours on that Sunday.

    I've no idea how to do this, any ideas? Am open to VBA solutions, but am not good enough there to code it myself.

    Thanks!

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,947

    Re: Calculate hours between 2 dates - exclude weekend hours

    Try replacing all references to Date1 with

    =IF(WEEKDAY(Date1,2)>5,INT(Date1)+(8-WEEKDAY(Date1,2)),Date1)

    which will return Monday at 0:00 if Date1 is a weekend day. You may need to do something similar to Date2, like this

    =IF(WEEKDAY(Date2,2)>5,INT(Date2)-WEEKDAY(Date2,2)+6,Date2)

    which will return Saturday at 0:00 (or Friday at 12:00 PM) if date 2 is a weekend.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    05-08-2015
    Location
    Atlanta, GA
    MS-Off Ver
    2013
    Posts
    5

    Re: Calculate hours between 2 dates - exclude weekend hours

    Thanks Bernie, I've actually been going down that road for the past hour. Using INT and resetting the start/end date to the end/beginning of the weekend seems like my best bet. Just that my formula gets really long (there are some other criteria I didn't mention).

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,947

    Re: Calculate hours between 2 dates - exclude weekend hours

    Instead of replace the Date1 and Date2 references in your formulas to the longer IF formulas, you can use 2 helper cells that change the dates being used by your longer formula, so that it doesn't get too long.

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

    Re: Calculate hours between 2 dates - exclude weekend hours

    You can use this formula

    =NETWORKDAYS(date1,date2)+NETWORKDAYS(date2,date2)*(MOD(date2,1)-1)-NETWORKDAYS(date1,date1)*MOD(date1,1)

    If you also need to exclude holidays you can add a holiday range to all three NETWORKDAYS functions
    Audere est facere

+ 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. Calculate Work Hours Between Dates & Exclude Holidays
    By mycon73 in forum Excel General
    Replies: 5
    Last Post: 10-29-2014, 01:02 AM
  2. [SOLVED] How To Calculate Hours Between 2 or 3 Days Exclude Non Working Hours?
    By Fazrullah Jaini in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-03-2014, 09:54 AM
  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: 03-02-2013, 10:57 AM
  5. Replies: 4
    Last Post: 10-31-2005, 05:05 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