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.
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)
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.
- 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 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.
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)
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
- 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.
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)
Formula Breakdown
NETWORKDAYS(C6,D6) → It determines the workdays available between D6 and C6.
      Output → 6
      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.
      Output → 6
      Explanation ⇒ 2/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)
      Output → 0
      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.
- 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.
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
- How to Calculate Years of Service in Excel
- How to Calculate Turnaround Time in Excel
- How to Calculate Average Handling Time in Excel
- How to Calculate Average Response Time in Excel
- How to Calculate Average Turnaround Time in Excel
<< Go Back to Calculate Time | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
