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

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.

Calculate turnaround time in excel excluding weekends


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)

Calculate turnaround time in excel excluding weekends

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.

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 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.

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 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)

Calculate turnaround time in excel excluding weekends

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

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 Total Hours Worked in a Week in Excel (Top 5 Methods)


Similar Readings


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 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)
           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 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

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo