How to Calculate Turnaround Time in Excel Excluding Weekends – 3 Methods

This is the sample dataset.

Calculate the Turnaround Time in days and then in Hours.

Calculate turnaround time in excel excluding weekends


Method 1 – Using the NETWORKDAYS Function to Calculate the Turnaround Time in Excel Excluding Weekends

Use the NETWORKDAYS function.

STEPS:

  • Go to E6 and enter the formula.
=NETWORKDAYS(C6,D6)

Calculate turnaround time in excel excluding weekends

Excel finds the days between the Start Date in C6 and the End Date in D6.

  • Press ENTER.

Excel will return the number of days between 2/28/2020 and 2/21/2020 excluding weekends.

Calculate turnaround time in excel excluding weekends

  • Drag down the Fill Handle to AutoFill the rest of the cells.

Calculate turnaround time in excel excluding weekends

To convert the days to hours:

  • In D4, you have 8 Working Hours per Day. Multiply the Turnaround Time by Working Hours per Day.
  • Select F6 and enter the formula:
=E6*$D$3

E6 was multiplied by D3. An Absolute Cell Reference was used in D3.

  • Press ENTER.

Excel will return the Turnaround Time in Hours.

Excel multiplied 6 by 8 to get 48 in F6.

  • Drag down the Fill Handle to AutoFill the rest of the cells.

Calculate turnaround time in excel excluding weekends

NOTE: The NETWORKDAYS function considers weekends as Saturday and Sunday by default.

Read More: How to Calculate Elapsed Time in Excel


Method 2 – Applying the NETWORKDAYS.INTL Function to Calculate the Turnaround Time in Excel Excluding Weekends

Use the NETWORKDAYS.INTL function. This is an advanced version of the NETWORKDAYS function, in which you can choose the weekends.

STEPS:

  • Go to E6 and enter the formula.
=NETWORKDAYS.INTL(C6,D6,7)

Calculate turnaround time in excel excluding weekends

The Start Date is in C6 and the End Date in D6. 7 indicates that the weekends are Friday and Saturday.

  • Press ENTER.

Excel will return the number of days between 2/28/2020 and 2/21/2020 excluding weekends.

Calculate turnaround time in excel excluding weekends

  • Drag down the Fill Handle to AutoFill the rest of the cells.

Calculate turnaround time in excel excluding weekends

Calculate the Turnaround Time in Hours following Method 1.

Calculate turnaround time in excel excluding weekends

Read More: How to Calculate Slack Time in Excel


Method 3 – Using Combined Functions to Calculate the Turnaround Time Excluding Weekends

Use the NETWORKDAYS function, the MOD function, the WEEKDAY function, and the IF function.

NETWORKDAYS counts from the beginning of the first day to the end of the last day. If both the start and end dates are weekdays, an extra day is added. To correct this, subtract the beginning portion of the first day (midnight to start time) and the final part of the last day (end time to midnight) if they are both workdays.

STEPS:

  • Go to E6 and enter the formula.
=NETWORKDAYS(C6,D6)+IF(MOD(WEEKDAY(D6),7)>1,MOD(D6,1)-1,0)-IF(MOD(WEEKDAY(C6),7)>1,MOD(C6,1),0)

Calculate turnaround time in excel excluding weekends

Formula Breakdown

NETWORKDAYS(C6,D6) → determines the workdays available between D6 and C6.
           Output6
           Explanation ⇒ there are 6 working days excluding the weekends between 2/28/2020 and 2/21/2020.
WEEKDAY(D6) → identifies the day of the week in D6.
           Output6
           Explanation2/28/2020 was the 6th day of that week: Friday.
IF(MOD(WEEKDAY(D6),7)>1,MOD(D6,1)-1,0)  returns the reminder after dividing number by divisor.
       IF(6>1,-1,0)
           Output-1
           Explanation ⇒ Since 6>1, we get -1 as value_if_true is -1
IF(MOD(WEEKDAY(C6),7)>1,MOD(C6,1),0) returns the reminder after dividing number by divisor.
       IF (6>1,0,0)
           Output0
           Explanation ⇒ Since 6>1, we get 0 as value_if_true is 0

  • Press ENTER.

Excel will return the number of days between 2/28/2020 and 2/21/2020 excluding weekends and subtracting the beginning portion of the first day (midnight to start time) and the final part of the last day (end time to midnight).

Calculate turnaround time in excel excluding weekends

  • Drag down the Fill Handle to AutoFill the rest of the cells.

Calculate the Turnaround Time in Hour following Method 1.

Calculate turnaround time in excel excluding weekends

Read More: How to Calculate Cycle Time in Excel


Practice Workbook

Practice here.


Download Practice Workbook


Related Articles


<< Go Back to Calculate Time | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo