Akib Bin Rashid
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.
Download Practice Workbook
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 Turnaround Time in Hour.
3 Methods to Calculate Turnaround Time in Excel Excluding Weekends
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 in 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 as Working Hour per Day. To find out the Turnaround Time in Hour, we simply need to multiply the Turnaround Time with Working Hour 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 with 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 Turnaround Time in Excel (4 Ways)
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 basically 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 End Date in D6 as my inputs. 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 Total Hours Worked in a Week in Excel (Top 5 Methods)
Similar Readings
- [Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)
- How to Subtract Time in Excel (7 Quick Methods)
- Excel Formula To Calculate Time Worked
- How to Subtract Date and Time in Excel (6 Easy Ways)
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 diving 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 diving 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 Subtract Minutes from Time in Excel (7 Methods)
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.
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.
Excel with us.
Related Articles
- How to Subtract Military Time in Excel (3 Methods)
- Subtract and Display Negative Time in Excel (3 Methods)
- How to Calculate Hourly Rate in Excel (2 Quick Methods)
- Use Time Format in Excel VBA (Macro, UDF, and UserForm)
- Excel Formula to Calculate Overtime and Double Time (3 Ways)
- Calculate Average Handling Time in Excel (2 Easy Ways)
- How to Calculate the Duration of Time in Excel (7 Methods)
- Calculate Hours and Minutes in Excel (7 Handy Ways)
