Calculate Time taken to process with network days

Question

I am looking for some assistance in calculating the time taken between 2 dates and times to only look at weekdays.

The date/time I will be using is in the following format:

30/10/15 08:00:00

Date 1 will be in column D and date 2 will be in column E

The formula I have applied looks as follows:

=INT(E2-D2)&" Days "&HOUR(MOD(E2-D2,1))&" Hour "&MINUTE(MOD(E2-D2,1))&" Minutes"

I am looking to update this formula so it only works on weekdays via the networkdays function but am unsure how to apply within the above formula.

The issue is requests that come in on a Friday and processed on Monday are reflecting 3 days to process, the ideal situation is the calculation would only reflect 1 day and excludes weekends.

The spreadsheet should look as follows:

Data for the cells are:

Date/time approved:

02/02/16 08:00:00
03/02/16 08:00:00
03/02/16 08:00:00
03/02/16 08:00:00
03/02/16 15:00:00
03/02/16 15:00:00
03/02/16 15:00:00
03/02/16 15:00:00
03/02/16 15:00:00
03/02/16 15:00:00
03/02/16 15:00:00
03/02/16 15:00:00
01/02/16 08:00:00

Date/time Completed

04/02/16 10:30:00
04/02/16 07:40:00
04/02/16 07:55:00
04/02/16 07:19:00
04/02/16 12:22:00
04/02/16 12:22:00
04/02/16 12:22:00
05/02/16 06:45:00
05/02/16 06:35:00
05/02/16 06:01:00
05/02/16 06:01:00
05/02/16 06:01:00
01/02/16 10:23:00

Column F should show the time taken to process excluding weekends

And column G will have a Boolean of: IF(E2-D2 <= 1, "Y", "N")

And column H will have a Boolean of: IF(E2-D2 <= 2, "Y", "N")

Which will calculate if SLA has been met or not

Unknown Unknown · Accepted Answer · 2016-02-24T08:19:40

Try to use the NETWORKDAYS.INTL¹ function instead of the older NETWORKDAYS function. While the additional functionality is not particularly needed here, it is good practise to get familiar with using one function rather than different versions of the same thing.

In C2 as a standard formula,

=NETWORKDAYS.INTL(A2, B2,1, Z2:Z9)-1+
     (TIMEVALUE(TEXT(B2,"hh:mm"))-TIMEVALUE(TEXT(A2,"hh:mm")))

Format the C2 cell with a custom number format of d \d\a\y\s h \hou\r\s m \mi\nut\e\s. This leaves the raw underlying value available for further calculation.

¹ _{The NETWORKDAYS.INTL function was introduced with Excel 2010. It is not available in earlier versions.}

Calculate Time taken to process with network days

1 Answers