Md. Ahaduzzaman
In our everyday life, we have to keep track of the different tasks on a regular basis. We may need this record to calculate the turnaround time. So it is very common for a company to keep a record of the turnaround time of their products so they can understand how much time is necessary to deliver. It can be helpful to prepare a product delivery plan and create a model to reduce the ideal time. We can also increase the efficiency of the delivery system if we have the turnaround data in our hands. So we will discuss 4 different ways to calculate turnaround time in Excel in this article. We will use different excel functions such as TEXT, MOD, NETWORKDAYS, DAYS360, etc. You can take a glance at the overview given below to learn the Methods and the Outcomes of this article.
📁 Download Excel File
Download the practice file from here.
Overview of Turnaround Time
Turnaround time is the time that is required to fulfill a process. In an industrial setup, it denotes the time difference from when we place an order to the time the order is fulfilled. It is very important for us to properly calculate the turnaround time because this time difference indicates the time period when the factory or an industry is not operating. Basically, at this time the factory is not making any profit. So the less we can keep the turnaround time the better it is for the profit margin. Less turnaround time also means a faster production rate. The formula for Turnaround time is as follows.
Turnaround Time=Completion Time-Arrival Time
How to Calculate Turnaround time (TAT) Between Two Times in Excel
Turnaround time is actually a small part of the total time in a particular process. So at first, we need to understand different times relating to a process. You will find the different times related to a process below.
- Arrival Time: This time denotes the particular time when a process arrives in the queue.
- Waiting Time: This time indicates the amount of time a process has to wait in the queue. We can get the Waiting Time by deducting the Burst Time from Turnaround Time.
- Response Time: It indicates the time amount after which a process gets the CPU for the first time.
- Burst Time: We also call it Execution Time or Running Time. It is the time a process takes while executing on the CPU.
- Completion Time: We also call it Exit Time. It denotes the time when a process completes its execution on the CPU & exits the system.
- Turnaround Time: It is the total time a process takes when it is in the system. We can measure it by adding the Burst Time & Waiting Time. We can further measure it by deducting the Arrival Time from the Completion Time.
Learn to Calculate Turnaround Time in Excel with These 4 Methods
In the following sections, we will discuss 4 simple ways to calculate turnaround time in Excel. Let us assume that we have the Product Delivery Sheet of a company. We know the Loading and Arrival times of the different products. Now we want to know the Turnaround time of the individual products. You can take a look at our sample dataset for a better understanding.
1. Utilizing TEXT Function
In this section, we will apply the TEXT function to calculate turnaround time in Excel. This function converts a number value into a text format.
Overview of TEXT Function
TEXT function is capable of formatting a number or date into text. The syntax for this function is =TEXT(Value you want to format, “Format code you want to apply”). For instance, we can use the TEXT function as such.
=TEXT(B1, “0.00%”)
Here the TEXT function takes the cell value B1 and returns us a percentage having two decimals.
1.1 Display Hours
The steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell E6 formula box to put this formula.
=TEXT(D6-C6,"hh")
- After that, we use the Fill Handle tool to drag the formula.
- Finally, we get our result in column E.
1.2 Display Minutes
The breakdown steps for this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we type this formula in the cell E6 formula box and press the Enter button.
=TEXT(D6-C6,"[mm]")
- Next, we apply the Fill Handle tool to copy the formula.
- Finally, we get the result in column E.
1.3 Display Seconds
In this section, we will display the result in Seconds. So the steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we put this formula in the cell E6 formula box.
=TEXT(D6-C6,"[ss]")
- Following that, we apply the Fill Handle tool for dragging the formula.
- Finally, we get the result in column E.
1.4 Display Hours & Minutes
In this section, we will display the text return in the Hour & Minute format. So the steps for applying this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we write this formula in cell E6 and press the Enter button.
=TEXT(D6-C6,"[hh]:mm")
- After that, we use the Fill Handle tool to copy the formula.
- Finally, we get our result in column E.
1.5 Display Hours, Minutes & Seconds
In this section, we will display our results in the Hour, Minute & Second format.
So the steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we write this formula in the cell E6 formula box.
=TEXT(D6-C6,"[hh]:mm:ss")
- Now we use the Fill Handle tool for dragging the formula.
- Finally, we get the result in column E.
2. Applying MOD function
In this section, we will apply the MOD function to calculate turnaround time in Excel. This function returns the remainder of a number after dividing by a divisor.
Overview of MOD Function
MOD function returns us the remainder after devising it with a divisor. The syntax for this function is MOD(number, divisor). For example, we can use the function as such.
=MOD(B1, C1)
In this illustration, the MOD function divides the values of cells B1 by C1 to determine the residual. The function’s output will be a numeric representation of the residual. The MOD function will return 2 if B1 contains the value 20 and C1 contained the value 6.
The steps for applying this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we write this formula in the cell E6 formula box.
=MOD(D6-C6,1)*24
- So here we use 1 as the divisor and multiply the result by 24 to get the hour as a return.
- Following that we use the Fill Handle tool to copy the formula.
- Finally, we get the result in column E.
3. Implying NETWORKDAYS Function
In this section, we will apply the NETWORKDAYS function to calculate the Turnaround time in Excel. This function returns us the entire workday between two separate days.
Overview of NETWORKDAYS Function
NETWORKDAYS function is very useful to determine the number of workdays excluding the holidays. The syntax for this function is NETWORKDAYS(start_date, end_date, [holidays]). For instance, we can use this function like this.
=NETWORKDAYS(B1, C1)
The NETWORKDAYS function in this example determines how many working days there are between the dates in cells B1 and C1. A number that represents the number of working days between the two dates will be the function’s output. The NETWORKDAYS function returns an error if either B1 or C1 is empty or has an incorrect date.
3.1 In Days
This method provides us with the result in Days. The steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we type this formula in the cell F6 formula box.
=NETWORKDAYS(C6,D6)
- Following that we use the Fill Handle tool to copy our formula.
- Finally, we get our result in column F.
3.2 In Hours
In this section, we will display our results in the Hour format. The steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we write this formula in the cell F6 formula box.
=NETWORKDAYS(C6,D6)*8
- Here we take the 8 hours as daily workhour.
- Now we use the Fill Handle tool to copy the formula.
- Finally, we get the necessary result in column F.
4. Applying DAYS360 Function
In this section, we will imply DAYS360 to calculate turnaround time in Excel. This function returns us the day difference between two separate days considering 360 days as one year.
Overview of DAYS360 Function
DAYS360 function assumes every month as 30 days and returns the difference between two days considering 360 days a year. For instance, we can use a function like this.
=DAYS360(B1, C1)
The DAYS360 function in this example uses 360 days a year to determine how many days there are between the dates in cells B1 and C1. A number that depicts the number of days between the two dates based on a year with 360 days will be the function’s output. The DAYS360 function will give an error if either B1 or C1 is empty or include any invalid date.
The steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we type this formula in the cell E6 formula box & press the Enter button.
=DAYS360(C6,D6)
- After that, we use the Fill Handle tool for dragging the formula.
- Finally, we get the result in column E.
How to Calculate Turnaround Time in Excel Excluding Weekends and Holidays
In this section, we will apply the NETWORKDAYS function to calculate Turnaround Time in Excel excluding weekends and holidays. So we will not include the holidays into account while determining the Turnaround time. So the steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell E6 formula box and write this formula.
=NETWORKDAYS(C6,D6,$G$6:$G$11)
- Here $G$6:$G$11 indicates the list of holidays that we want to omit.
- After that, we use the Fill Handle tool to copy the formula.
- Finally, we get our result in column E.
How to Calculate Average Turnaround Time (TAT) in Excel
In this section, we will apply the AVERAGE function to calculate the Average Turnaround Time in Excel. This function provides us with the average of a data range.
Overview of AVERAGE Function
The AVERAGE function returns us the average of a given number range. The syntax for this function is AVERAGE(number1, [number2], …). We can imply the function as this.
=AVERAGE(B1:B10)
The AVERAGE function in this illustration determines the average value between the cells B1 to B10. A number that represents the average of the values in the given range will be the function’s output. The AVERAGE function will produce an error if any of the range’s cells are empty or have an incorrect value.
The steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell E6 formula box to put this formula.
=D6-C6
- Now we copy the formula in column E using the Fill Handle tool.
- Now we write this function in cell E13 and press the Enter button.
=AVERAGE(E6:E11)
- Finally, we get the result in cell E13.
How to Calculate Turnaround Time (TAT) Percentage in Excel
In this section, we will apply the SUM function to calculate the Turnaround Time percentage in Excel.
This function returns us the summation of a given data range.
Overview of SUM Function
The SUM function provides us with the summation of a given number range. The syntax for this function is SUM(number1,[number2],…). For instance, we can apply the function like this.
=SUM(B1:B10)
The SUM function in this illustration computes the summation of the values in cells B1 through B10. A number that represents the total of the values in the given range will be the function’s output. The SUM function will return an error if any of the range’s cells are empty or have an incorrect value.
So the steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell E13 formula box to write this formula.
=SUM(E6:E11)
- Now we get the Total Turnaround Time in cell E13 as a return.
- After that, we write this formula in the cell F6 formula box.
=E6/$E$13
- Following that we use the Fill Handle tool to drag the formula.
- Finally, we get the result in column F.
📄 Important Notes
🖊️ When we apply the Excel functions we need to be careful about the function arguments.
🖊️ The TEXT function to display the hour method provides the hour value only after truncating the minutes.
📝 Takeaways from This Article
📌 Firstly we can use the TEXT function to calculate turnaround time in Excel.
📌 We can also apply the MOD function to calculate turnaround time in Excel.
📌 Implying the NETWORKDAYS function can be another way to do the task.
📌 Moreover we can apply the DAYS360 function to calculate turnaround time in Excel.
Conclusion
In this article, we have learned 4 different ways to calculate turnaround time in Excel. We can use different Excel functions such as TEXT, MOD, NETWORKDAYS, DAYS360, etc., to calculate Excel’s turnaround time. So the users can use any one of these functions to do their tasks. Also if you have any queries regarding this article you can write your question in the comment. The writer will try to answer them. Moreover, if you want to explore more Excel-related problems and solutions please visit our website www.Excelden.com.
