Abrar
Calculating turnaround time can be an easy task for a small range of people. But when we want to do so for a large dataset, we need the help of spreadsheets that can make our task easy. We will talk about the ways those can be used for calculating turnaround time excluding weekends in Excel. This is an overview of the approaches that will be used and the respective outputs from each approach.
📁 Download Excel File
Download the practice workbook below.
What is Turnaround Time?
Turnaround time is when a process or a request takes before fulfillment. It is the time interval from the time of submission to the time of completion. In computing terms, turnaround time refers to the time that a program takes starting from its execution to the delivery of output after the launch of the program. In logistics, turnaround time is the time from the submission of a fulfillment request to the time the order has been fulfilled. Turnaround time is important as the less this time is, the faster the companies can earn profit. For instance, the faster one can complete their role, the faster the product goes into the process of completion.
Learn to Calculate Turnaround Time Excluding Weekends in Excel with These 3 Approaches
Here, we will look at 3 different approaches by which we can determine turnaround time excluding weekends in Excel. To demonstrate things easily, we will consider a dataset where we have the employee names, start and end dates, and working hours per day. The dataset is shown as follows:
We will use these data to determine turnaround time with the following approaches.
1. Incorporating NETWORKDAYS Function
NETWORKDAYS function can be used as a simple approach for calculating turnaround time. The steps for this approach are as follows:
⬇️⬇️ STEPS ⬇️⬇️
- First, we choose cell F6, then insert the formula below.
=NETWORKDAYS($C6,$D6)
🔨 Formula Breakdown
NETWORKDAYS($C6,$D6)
👉 The first argument in cell $C6 denotes start date.
👉 The second argument in cell $D6 denotes end date.
- Next, we press Enter.
- Then, we drag Fill Handle to cell F12.
- This will provide us with a turnaround time value in days for all employees.
- After that, we move to cell G6 and insert the following formula.
=F6*$E$6
- Turnaround time will convert into hours for cell G6. Now, we drag Fill Handle of cell G6 to cell G12.
- The final output results will look like the following image.
📕 Read More: Calculate Hours Between Two Dates and Times in Excel Excluding Weekends
2. Inserting Excel NETWORKDAYS.INTL Function with Custom Weekend
In this approach, we will use NETWORKDAYS.INTL function to find out turnaround time value in days and then hours. This is an advanced form of NETWORKDAYS function, by which we can customize weekends in an argument. The steps for this approach are as follows:
⬇️⬇️ STEPS ⬇️⬇️
- At first, we move to cell F6.
- After that, insert the formula below.
=NETWORKDAYS.INTL($C6,$D6,7)
🔨 Formula Breakdown
NETWORKDAYS.INTL($C6,$D6,7)
👉 The first argument in cell $C6 denotes the start date.
👉 The second argument in cell $D6 denotes the end date.
👉 7 indicates the weekend number where the weekends are on Friday as well as Saturday.
- After that, we press Enter.
- Now, drag Fill Handle icon at the bottom right of cell F6 to cell F12.
- Thus, we will get turnaround times in days.
- Then, we select cell G6 and insert the following formula.
=F6*$E$6
- Cell G6 will show the value in hours. Next, we drag Fill Handle at the bottom right of cell G6 to cell G12.
- The output results will look like the following image.
📕 Read More: 7 Examples to Calculate Duration of Time in Excel
3. Combination of Multiple Functions for Turnaround Time
In this approach, we will make use of combined functions to create a formula in order to find out turnaround time. The functions include IF, NETWORKDAYS, MOD, and WEEKDAY functions. This method is a bit different than the other two. It is because, when we use NETWORKDAYS function, it calculates the days starting from the commencement of the 1st date to the ending of the second date. If both of such days are workdays, we need to subtract the commencement of the 1st day (From midnight to Begin Time) and last portion of the ending day (Ending time until Midnight). This combined function makes sure of that. The steps for this approach are as follows:
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, we move to cell G6 and enter the following 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)+IF(MOD(WEEKDAY($D6),7)>1,MOD($D6,1)-1,0)-IF(MOD(WEEKDAY($C6),7)>1,MOD($C6,1),0)
👉 The portion NETWORKDAYS($C6,$D6) indicates the number of days except for weekdays between the start date in cell C6 and end date in cell D6.
👉 WEEKDAY($D6) denotes the nth day of weekday date in cell D6 is. For weeks starting from Sunday, we can say we will obtain a value of 5 if date in cell D6 is a Thursday.
👉 MOD(WEEKDAY($D6),7) function has the first argument as number and the second argument 7 as the divisor.
👉 IF(MOD(WEEKDAY($D6),7)>1,MOD($D6,1)-1,0) portion of the formula suggests that, under the IF function, the first argument MOD(WEEKDAY($D6),7)>1,MOD($D6,1)-1 is the condition and the second argument 0 is the value to return if the condition is satisfied or true.
- After that, we press Enter.
- Now, drag Fill Handle at the bottom right of cell F6 to cell G12.
- Turnaround time values in days will be visible now.
- Then, we move to cell G6 and insert the following formula.
=F6*$E$6
- We drag Fill Handle again at the bottom right of cell G6 to cell G12.
- The results will look like the following image.
📕 Read More: 5 Ways to Calculate Total Hours Worked in a Week in Excel
📄 Important Notes
🖊️ NETWORKDAYS and NETWORKDAYS.INTL functions calculate time from the beginning of the 1st date to the ending of the second date.
🖊️ NETWORKDAYS.INTL allows us to choose the weekends, unlike NETWORKDAYS function.
📝 Takeaways from This Article
You have taken the following summed-up inputs from the article:
📌 We can use NETWORKDAYS function to forecast turnaround time. This is the simplest way.
📌 We can also specify weekends and therefore to do that, we have to use NETWORKDAYS.INTL to measure turnaround time.
📌 Lastly using combined functions, we can get rid of the extra time accounted in the previous two approaches.
Conclusion
To conclude, I hope that this article has helped you to understand how to calculate turnaround time excluding weekends in Excel. For any kind of query, reach out to us by leaving a comment below. Follow our website ExcelDen for more informative articles related to Excel.
Related Articles
- 4 Easy Ways to Calculate Percentage of Time in Excel
- How to Calculate Average Response Time in Excel
- 9 Tricks to Calculate Time Difference Between AM and PM in Excel
- 4 ways to Create Excel Formula for Overtime Over 8 Hours
- 2 Ways to Calculate Travel Time Between Two Cities in Excel
- How to Calculate Overtime and Double Time with Formula in Excel
