2

I am attempting to calculate the time difference between two dates to exclude weekends and holidays in Excel. The issue I am running into is that sometimes an event happens during a weekend.

For example I am dealing with system inputs to calculate the time between when something was added to the system and when someone worked the issue.

I have the input time in Cell A1 and the completed time in Cell B1. I currently use the following formula NETWORKDAYS but I don't like how that returns full days instead I would like to know the hours. When using the NETWORKDAYS formula along with MOD I get an error for anytime something happened during the weekend.

I am dealing with a system where anyone can input something at any time but we do not respond during non business hours unless the system automtically responds for us.

1
  • Have you looked at the WORKDAY function? That deals with whole day counts. If you are talking about hours, do you just want to convert a date/time difference minus excluded days to hours or are you talking about "business hours", where only hours during the workday count?
    – fixer1234
    May 9, 2015 at 4:39

2 Answers 2

0

There is no built-in NETWORKHOURS function. You will have to implement it yourself or find examples online.

0

Does this work?

1) Enter Start Day/Time in cell A1 and End Day/Time in cell B1

Start/End Date/Time

2) Create a named range called Holidays, which contains any holidays. Example of creating a named range.

3) Copy the below formula, paste into the Formula Bar for cell D1, and press Enter.

=IFERROR(MROUND(NETWORKDAYS(
IF(OR(
          WEEKDAY(A1,2)>=6,
          COUNTIF(Holidays,INT(A1))>0),WORKDAY(A1,1),
     A1),
IF(OR(
          WEEKDAY(B1,2)>=6,
          COUNTIF(Holidays,INT(B1))>0),WORKDAY(B1,-1)+TIME(23,59,59),
     B1))-1-MOD(
IF(OR(
          WEEKDAY(A1,2)>=6,
          COUNTIF(Holidays,INT(A1))>0),WORKDAY(A1,1),
     A1),1)+MOD(
IF(OR(
          WEEKDAY(B1,2)>=6,
          COUNTIF(Holidays,INT(B1))>0),WORKDAY(B1,-1)+TIME(23,59,59),
     B1),1),"1:00"),0)

Example:

Formula Example

4) Format cell D1 as Time with a Type of 37:30:55

Format Cells

5) Result example with start day/time on Saturday.

Result Example

Note: Above only shows one hour because the working day starts at midnight.

==============================================

Expected Results

1) Return working hours between specified date/time.

2) Return 0 if there <=0 working hours between the specified date/time (i.e. start/end on same weekend).

3) Round to the nearest hour.

4) A workday is considered 24 hours. You could probably modify the formula for specific working hours, but that was not specified in the question.

==============================================

Sources Used

How to Calculate Hours in Excel Excluding Weekends

Extract a Date from a Date-Time Number in Excel

How can I test if a date is a workday?

How to round time to nearest hour in Excel?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .