+ Reply to Thread
Results 1 to 4 of 4

Trying to calculate number of actual hours between two dates excluding weekends

  1. 03-23-2016, 02:01 AM #1
    wkwieland
    wkwieland is offline
    Registered User
    Join Date
    03-23-2016
    Location
    Orlando
    MS-Off Ver
    Excel 2010
    Posts
    2

    Trying to calculate number of actual hours between two dates excluding weekends

    Hi,

    I'm trying to create a formula which will calculate the weekday hours only, no weekends or holidays. I've seen numerous posts which mainly focus on working hours (0900-1700), but I just need weekday hours.

    For example...

    Start End
    Fri 1/29/16 08:32 Sat 1/30/16 00:31

    I would like for the formula to exclude the 31 minutes into Saturday, and provide a result (in this case) of 0.64444444. I've tried NETWORKDAYS+MOD but it's not producing the correct result.

    Please, any help would be great!!

    Thanks,
    Bill
    Reply With QuoteRegister To Reply
  2. 03-23-2016, 02:46 PM #2
    dflak
    dflak is offline
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: Trying to calculate number of actual hours between two dates excluding weekends

    The attached spreadsheet shows the intermediate calculations in the helper columns (C:H). The combined formulas are in columns K:L.

    With the start time in Cell A2 and the end time in Cell B2

    Revised Start =IF(WEEKDAY(A2,2)>5,INT(A2+7-WEEKDAY(A2,2)+1),A2)
    Revised End =IF(WEEKDAY(B2,2)>5,INT(B2-7-WEEKDAY(B2,2)+1),B2)

    elapsed time would be =IF(WEEKDAY(B2,2)>5,INT(B2-7-WEEKDAY(B2,2)+1),B2) - IF(WEEKDAY(A2,2)>5,INT(A2+7-WEEKDAY(A2,2)+1),A2)
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
    Reply With QuoteRegister To Reply
  3. 03-23-2016, 02:49 PM #3
    dflak
    dflak is offline
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: Trying to calculate number of actual hours between two dates excluding weekends

    The attached spreadsheet shows the intermediate calculations in the helper columns (C:H). The combined formulas are in columns K:L.

    With the start time in Cell A2 and the end time in Cell B2

    Revised Start =IF(WEEKDAY(A2,2)>5,INT(A2+7-WEEKDAY(A2,2)+1),A2)
    Revised End =IF(WEEKDAY(B2,2)>5,INT(B2-7-WEEKDAY(B2,2)+1),B2)

    elapsed time would be =IF(WEEKDAY(B2,2)>5,INT(B2-7-WEEKDAY(B2,2)+1),B2) - IF(WEEKDAY(A2,2)>5,INT(A2+7-WEEKDAY(A2,2)+1),A2)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 03-23-2016, 05:42 PM #4
    wkwieland
    wkwieland is offline
    Registered User
    Join Date
    03-23-2016
    Location
    Orlando
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Trying to calculate number of actual hours between two dates excluding weekends

    Brilliant! Thank you dflak!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Difference between 2 dates - excluding working hours, weekends and holidays
    By ray2 in forum Excel General
    Replies: 13
    Last Post: 07-20-2016, 02:05 AM
  2. Formula to calculate SLA by excluding non-business hours, weekends and holidays
    By [email protected] in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-04-2016, 11:42 PM
  3. Difference between 2 dates - excluding working hours, weekends and holidays
    By dia in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-30-2014, 01:22 AM
  4. How do you calculate the hours between two dates excluding the weekends?
    By Charoum in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-03-2013, 08:10 PM
  5. Calculate response time between two dates, in hours, excluding holidays/weekends
    By meanjean312 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-10-2012, 11:41 AM
  6. Calculate difference between two dates, in hours, excluding holidays/weekends
    By ut2282v in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-11-2011, 06:26 AM
  7. Calculate difference in date/time excluding non-working hours and Thurs/Fri weekends
    By MAshleigh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-23-2010, 04:46 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1