How to Calculate Turnaround Time in Excel Excluding Weekends (3 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Excel is undoubtedly one of the most useful tools. We can use Excel to do myriads of things. For example, we often need to calculate the turnaround time for a project. We can easily calculate turnaround time in Excel excluding weekends. In this article, I am going to show you 3 methods to calculate turnaround time in Excel excluding weekends.

This is the dataset that I am going to use to show how to calculate turnaround time in Excel excluding weekends. Here, we have the Steps required to complete a project with the Start Date and End Date. From the Start Date and End Date, we will calculate the Turnaround Time in days and then the Turnaround Time in Hours.

Calculate turnaround time in excel excluding weekends


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

1. Using NETWORKDAYS Function to Calculate Turnaround Time in Excel Excluding Weekends

In this section, I will use the NETWORKDAYS function to calculate turnaround time in Excel excluding weekends.

STEPS:

  • Go to cell E6 and write down the formula
=NETWORKDAYS(C6,D6)

Calculate turnaround time in excel excluding weekends

Here, Excel will find out the days that are between the Start Date at C6 and the End Date at D6.

  • Then 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

  • Then use Fill Handle to AutoFill up to E10. You will see Excel calculating the turnaround time for all the cells up to E10.

Calculate turnaround time in excel excluding weekends

Now the Turnaround Time is in days. We will convert the days to hours. At cell D4, we have 8 Working Hours per Day. To find out the Turnaround Time in Hours, we simply need to multiply the Turnaround Time by Working Hours per Day. To do so,

  • Select cell F6 and write down the formula:
=E6*$D$3

Here we have multiplied the value in E6 with D3. We have used Absolute Cell Reference for D3 as we want the Working Hour per Day fixed throughout this worksheet.

  • Then press ENTER. Excel will return the Turnaround Time in Hour.

Excel has multiplied 6 by 8 to get 48 in cell F6.

  • Then use the Fill Handle to AutoFill up to cell F10.

Calculate turnaround time in excel excluding weekends

NOTE: The NETWORKDAYS function considers the weekends as Saturday and Sunday by default. That is a limitation of this function in Excel.

Read More: How to Calculate Elapsed Time in Excel


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

Now I will describe how to calculate turnaround time in Excel excluding weekends using the NETWORKDAYS.INTL function. This is an advanced version of the NETWORKDAYS function where you can choose the weekends.

STEPS:

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

Calculate turnaround time in excel excluding weekends

Here, I have the Start Date in C6 and the End Date in D6 as my input. 7 indicates that the weekends that Excel is going to exclude are Friday and Saturday. You will have many other options in this function to choose your weekends manually.

  • Then 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

  • Then use Fill Handle to AutoFill up to E10. You will see Excel calculating the turnaround time for all the cells up to E10.

Calculate turnaround time in excel excluding weekends

Now you can also calculate the Turnaround Time in Hour following method 1.

Calculate turnaround time in excel excluding weekends

Read More: How to Calculate Slack Time in Excel


3. Using Combined Functions to Calculate Turnaround Time Excluding Weekends

Now we are going to learn a special case using the NETWORKDAYS function, MOD function, WEEKDAY function, and IF function together.
NETWORKDAYS counts from the beginning of the first day to the end of the last day. As a result, if both the start and end dates are weekdays, it tends to add an extra day. To correct this, we should subtract the beginning portion of the first day (midnight to start time) and the final part of the last day (end time to midnight) only if they are both workdays.

STEPS:

  • Go to cell E6 and write down 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) → It determines the workdays available between D6 and C6.
           Output6
           Explanation ⇒ We have 6 working days excluding the weekends between 2/28/2020 and 2/21/2020.
WEEKDAY(D6) → It identifies which day of the week the date in D6 is.
           Output6
           Explanation2/28/2020 was the 6th day of that week, that is Friday.
IF(MOD(WEEKDAY(D6),7)>1,MOD(D6,1)-1,0) Here MOD returns us the reminder after dividing the 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) Here MOD returns us the reminder after dividing the number by divisor.
       IF (6>1,0,0)
           Output0
           Explanation ⇒ Since 6>1, we get 0 as value_if_true is 0

  • Then 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) only if they are both workdays.

Calculate turnaround time in excel excluding weekends

  • Then use Fill Handle to AutoFill up to E10. You will see Excel calculating the turnaround time for all the cells up to E10.

Now you can also 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

It is easy to calculate turnaround time in Excel excluding weekends. But it is good to practice to master this skill. That’s why I have attached a practice sheet for you all.


Download Practice Workbook


Conclusion

In this article, I have described how to calculate turnaround time in Excel excluding weekends. I hope this will help you. Lastly, if you have any suggestions or comments, please leave them in the comment box.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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