While working with Microsoft Excel date-time, you will have to perform a lot of things based on this. One of them is turnaround time. We use the turnaround time in a lot of Excel timesheets. You can say this is practical usage of the date and time formula of Excel. In this tutorial, you will learn to calculate the turnaround time in Excel with suitable examples and proper illustrations. So, let’s get into it.
Download Practice Workbook
What is Turnaround Time?
Turnaround time is the time needed for finishing a specific procedure or task from the moment we formally need it. Turnaround time usually guides the portion of time calculated from the submission of a request to the ending and delivery to the requester.
Nowadays, every organization uses the turnaround time in several circumstances because task fulfillment is a regular event in everyday routines. Most businesses like to comprehend their turnaround time for various actions and sub-activities. They try to minimize the turnaround time. Because reducing the turnaround time for critical operations is a typical administrative purpose. It improves efficiency and consumer fulfillment.
Organizations consider this as one kind of implementation metric. For instance, a company might observe how the span of time since a consumer puts order to its completion and delivery develops monthly.
After that, the company calculates the average of all orders submitted each month. Then the companies examine how they can reduce the time. Regarding high competition in today’s markets, businesses typically seek to finish the most significant operations in the most downtime attainable.
Example of Turnaround Time:
X Transportation Ltd. is a company that provides transportation assistance to several consumers. They basically pack products based on requests and deliver them to the client.
This company operates with 30 trucks to deliver those bear products from fifteen regular buyers to other companies.
Now, some of their top customers told them to reduce the turnaround time frequently. Here, the turnaround time is the time difference between the moment they put in a transportation request and the time when the product successfully reached its destination.
The X Transportation called a meeting to resolve this. The company acknowledged that the turnaround time changed from client to client because their loading procedure was occasionally complicated and extended at the consumer’s storehouses.
After that, the company formulated a project to reduce the loading time that will decrease turnaround time significantly. After the completion of the project, they discovered their loading time was reduced by 22% and that improved the turnaround time by 12%.
How to Calculate Turnaround time Between Two Times in Excel
Now, the turnaround time is the time difference between the start or arrival time and the completion time. You can also consider this the start date and end date to calculate the turnaround time.
To simply put:
Turnaround time = Completion time – Arrival time
In Microsoft Excel, you can simply subtract the start time from the completion time with the following formula:
=C5-B5
The problem you can notice here is we have the turnaround time in time format. As for the first data, we wanted 8 hours. Instead, we got to 8:00 AM.
You can format it in different ways. You have to just change it to Custom format to calculate the turnaround time in hours, minutes, and seconds format.
To change this follow these steps:
- First, select the range of cells.
- Now, press Ctrl+1 on your keyboard.
- Now, from the Format Cells dialog box, you will find various Numbers Next, select Custom from Category. Then, from the Type, select h:mm: ss format. Finally, click on OK.
In the end, it will display the turnaround time in hours, minutes, and seconds format. So, we are successful in calculating the turnaround time after performing a subtraction in the Excel timesheet.
Now, there are various ways you can calculate the turnaround time by performing the subtraction. We are going to show the most useful ones.
4 ways to Calculate Turnaround Time in Excel
In the upcoming section, I am going to show you four ways to calculate the turnaround time. Remember, use the first two methods if you are only working with time. Use the second two methods if you are working with dates in Excel. I will discuss them in later sections.
Here, I recommend you learn and apply all these methods to your Excel timesheet for calculating the around time. I hope it will develop your Excel knowledge and come in handy in the future.
1. Calculate Turnaround Time Using TEXT Function in Excel
In the previous section, we had to change the format of the time difference to calculate the turnaround time. Because Excel automatically changes the time format. For this reason, we had to change the time format.
Now, if you don’t want to face this hazard and want a simple solution, use the TEXT function. Here, you don’t have to worry about changing the format.
First, take look at the dataset.
Here, we have a dataset of a company that loads products and ships them. Here, you can see the arrival time of the product, loading time, and shipping time. After completing all of these processes the consumer gets the products.
Now, to calculate the time needed for completion, we are adding total hours to the arrival time.
The Generic Formula:
=arrival_time + (SUM(loading_time,shipping_time)/24)
Now, it’s time to calculate the turnaround time from this dataset.
The Generic Formula:
=TEXT(Completion Time – Arrival Time, Format)
Now, the first argument is basic subtraction. And in the format, you have to just enter the time difference format that you want.
1.1 Display Turnaround Time in Hours
To calculate the turnaround time in hours, use the following formula:
=TEXT(E5-B5,"hh")
This formula will only deliver the outcome that displays the number of hours difference for turnaround time in Excel. If your outcome is 10 hours and 40 minutes, it will display 9 hours only.
1.2 Display Only Minutes
To calculate turnaround time in only minutes worked, use the following formula:
=TEXT(E5-B5,"[mm]")
1.3 Display Only Seconds
To displayturnaround time in just seconds format, use the following formula:
=TEXT(E5-B5,"[ss]")
1.4 Display Hours and Minutes
If you want to calculate the turnaround time in hours and minutes format, use the following formula:
=TEXT(E5-B5,"[hh]:mm")
1.5 Display Hours, Minutes, and Seconds
For calculating turnaround time including all of these, use the following formula:
=TEXT(E5-B5,"hh:mm:ss")
Now, you may ask why we are using square brackets like [hh],[mm], or [ss] in somewhere. Basically, it gives you the whole number of Turnaround times in hours between the two dates, even if the hour is greater than 24. So if you want to calculate the turnaround time between two date values where the distinction is more than 24 hours, utilizing [hh] will deliver you the total number of hours for the turnaround time, and “hh” will just give you the hours passed on the day of the end date.
Read More: How to Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)
2. Using MOD Function to Calculate Turnaround Time in Excel
Now, if your time passes midnight, you will see the following turnaround time like the following:
It will display a negative value. To resolve these types of problems, a handy solution might be using the MOD function of Excel.
The Generic Formula:
=MOD(Delivery-Order_time,1)*24 hrs
This formula handles the negative time by utilizing the MOD function to “reverse” negative values to the demanded positive value. Because this formula will endure times on the exact day and times that pass midnight. This is the beauty of the MOD function.
Now, select Cell E5 and type the following formula:
=MOD(D5-C5,1)*24
As you can see, we are successful in calculating the turnaround time in Excel using the MOD function.
Read More: Excel Formula To Calculate Time Worked
3. Use of NETWORKDAYS Function to Calculate Turnaround Time
Now, if you are working with dates to calculate the turnaround time, use the NETWORKDAYS function. In Microsoft Excel, the NETWORKDAYS function counts the number of dates between two specific dates.
You may be in a company where you calculate the turnaround as days. To simply put, your turnaround is the time difference between the order placing date and the completion date.
The Generic Formula:
=NETWORKDAYS(start_date, completion_date)
Note to Remember: By default, the NETWORKDAYS function recognizes only Saturday and Sunday as weekends and you cannot customize these weekends. In this case, you have to use NETWORKDAYS.INTL function to customize weekends.
To demonstrate, we are using this dataset:
Here, you can see, we have dates that indicate the dates of order placement and delivery date. Here, we used the DAYS function to calculate the total days between the dates in the “Total Days” column. Now, according to the formula, our turnaround days will be the difference between these dates. But, as the company has weekends, the NETWORKDAYS function won’t consider them.
Now, you can also calculate the turnaround time in hours using the NETWORKDAYS function. In the upcoming section, we will discuss both.
3.1 Turnaround Time in Days
For calculating the turnaround time in days, select Cell E5 and type the following formula:
=NETWORKDAYS(B5,C5)
As you can see, we are successful to calculate the turnaround time in Excel.
3.2 Turnaround Time in Hours
Now, your company operates for a particular hour in a day. You can use this Excel formula to calculate the turnaround time in hours. You have to multiply the working hours with the function.
The Generic Formula:
=NETWORKDAYS(order date,delivery date)*working hours per day
For calculating the turnaround time in hours, select Cell E5 and type the following formula:
=NETWORKDAYS(B5,C5)*8
As you can see, we successfully used the NETWORKDAYS function to calculate the turnaround time in hours.
Read More: How to Calculate Total Hours in Excel (9 Easy Methods)
Similar Readings
- How to Add Time in Excel Over 24 Hours (4 ways)
- Excel Timesheet Formula with Lunch Break (3 Examples)
- How to Add Minutes to Time in Excel (3 Quick Methods)
- [Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)
4. Use of DAYS360 Function to Calculate
Now, if you don’t want to include the weekends or holidays in your around time, use the DAYS360 function.
The DAYS360 function calculates and returns the number of days between two dates based on a 360-day year (twelve 30-day months). You can use this in various situations.
The Syntax:
=DAYS360(start_date,end_date,[method])
Arguments:
Start_date, end_date: This is Required. These are the dates from where you want to know the difference. If your start_date happens after end_date, it will return a negative number. You should enter the dates in the DATE function or derive them from formulas or functions. For instance, use DATE(2022,2,22) to return on the 22nd day of February 2022. It will show some errors if you enter them as text.
Method: This is Optional. It is a logical value that determines whether to utilize the U.S. or European method in the analysis.
We are using the previous dataset to demonstrate this.
For calculating the turnaround time in days, select Cell E5 and type the following formula:
=DAYS360(B5,C5)
Read More: How to Calculate Time in Excel (16 Possible Ways)
How to Calculate Turnaround Time in Excel Excluding Weekends and Holidays
In the previous section, we used the NETWORKDAYS function to calculate the turnaround time in days or hours. This function doesn’t consider the weekends in the turnaround time. It automatically excludes the weekends.
Now, what if you have holidays in between the dates? Thankfully, you can solve this also using the NETWORKDAYS function.
The Generic Formula:
=NETWORKDAYS(start_date, completion_date, holidays)
Take a look at the following screenshot:
Here, we are using the previous dataset. But, look closely. We have now some holidays here. So, we have to calculate the turnaround time in Excel excluding the weekends holidays.
To calculate the turnaround time in days, select Cell E5 and type the following formula:
=NETWORKDAYS(B5,C5,$D$10:$D$18)
Here, we have used absolute cell references for the range of cells containing holidays. If you don’t use absolute cell references here, the cell references will change while auto-filling the cells in Column E. After that, the counting days will be wrong.
Related Content: How to Calculate Total Hours Worked in a Week in Excel (Top 5 Methods)
How to Calculate Average TAT in Excel
Now, calculating the TAT (turnaround time) is equivalent to calculating the average time in Excel. You have to add all the turnaround times and divide them by the number of projects, days, weeks, etc.
We will use the AVERAGE function here to calculate the average turnaround time.
Take a look at the following screenshot:
Here, we calculated the turnaround time for some projects. As you can see, we have three projects. We used the NETWORKDAYS function to calculate the turnaround time in days excluding the weekends.
We used the following formula to calculate the turnaround time in Excel:
=NETWORKDAYS(C5,D5)
Now, to calculate the average TAT in days, select Cell C9 and type the following formula:
=AVERAGE(E5:E7)
As you can see, we are successful to calculate the average turnaround time in Excel.
How to Calculate TAT Percentage in Excel
In this section, I will show you an example of a turnaround time percentage example. It will be an example of the increase in percentage for a particular turnaround time.
Before you practice this, read this: How to Calculate Percentage Increase in Excel (And Lots More!). It will give you a clear idea to calculate the percentage increase.
Take a look at the following screenshot:
Here, we have turnaround times of a company for two weeks. The company wants to improve its turnaround time. That’s why they made a change. Now, they reduced their loading and shipping time. As a result, it decreased their turnaround time. So, there is an improvement.
To calculate the turnaround time in hours, we used the TEXT function in the formula:
=TEXT(E5-B5,"hh")
The Generic Formula to Calculate the TAT percentage:
Increase in Percentage = (Week1 TAT – Week2 TAT) / Week1 TAT
Now, to calculate the TAT percentage in days, select Cell D12 and type the following formula:
=(F6-F10)/F6
As you can see, we are successful to calculate the turnaround time percentage in Excel.
Read More: How to Calculate Percentage of Time in Excel (4 Suitable Examples)
Things to Remember
✎ If the method returns the turnaround time in Time format, make sure to change them from the Numbers group.
✎ You have to be cautious about inputting the start_date and end_date. If your start_date is bigger than end_date, it will return a negative value.
✎ While inputting a range of cells containing holidays in the NETWORKDAYS function, make sure to use absolute cell references for the holiday’s argument.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge to calculate turnaround time in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
Related Articles
- Excel Formula to Calculate Hours Worked Minus Lunch
- Formula for Overtime over 40 Hours [with Free Template]
- Excel formula to calculate hours worked & overtime [with template]
- How to Calculate Difference Between Two Dates and Times in Excel
- Calculate Hours Between Two Times in Excel (6 Methods)
- How to Subtract Date and Time in Excel (6 Easy Ways)
- Calculate Elapsed Time Between Two Dates in Excel (5 Methods)