# How to Calculate Time Difference in Excel (13 Ways)

If you are looking for some of the easiest ways to calculate the time difference in Excel, then you are in the right place. Let’s dive into the main article to know the details of these ways.

## 13 Ways to Calculate Time Difference in Excel

Here, we have used the following two tables for demonstrating the examples of calculating time differences in Excel.

For creating the article, we have used Microsoft Excel 365 version, you can use any other versions according to your convenience.  ### Method-1: Using Arithmetic Operator to Calculate Time Difference in Excel

Here, we will determine the time differences between the Exit Times and the Entry Times to get the Working Hours of the employees by using minus signs. Step-01:
➤ Type the following formula in the cell E5

`=D5-C5`

It will subtract the Exit Time from the Entry Time. ➤ Press ENTER
➤ Drag Down the Fill Handle tool Result:
In this way, you will get the Working Hours of the employees. ### Method-2: Using TEXT Function to Calculate Time Difference in Excel

You can use the TEXT function to determine the time differences between the Exit Time and the Entry Time. Step-01:
➤ Type the following formula in the cell E5

`=TEXT(D5-C5,"hh:mm:ss")`
• D5-C517:00-8:30
Output→0.354166667
• TEXT(D5-C5,”hh:mm:ss”) becomes
TEXT(0.354166667,”hh:mm:ss”)
Output→08:30:00 ➤ Press ENTER
➤ Drag Down the Fill Handle tool Result:
Then, you will get the Working Hours of the employees. Similarly, for different formats, you can use the following functions

`=TEXT(D5-C5,"hh:mm")`

It will return the difference in hours and minutes `=TEXT(D5-C5,"hh")`

You will get the difference in hours here. Note
The TEXT function will return the differences in text format

Read More: How to Calculate Elapsed Time in Excel (8 Ways)

### Method-3: Using TIMEVALUE Function to Calculate Time Difference in Excel

Here, we will use the TIMEVALUE function for calculating the time differences between the Exit Time and the Entry Time. Step-01:
➤ Use the following formula in the cell E5

`=TIMEVALUE("17:00")-TIMEVALUE("8:30")`
• TIMEVALUE(“17:00”) becomes
0.708333333
• TIMEVALUE(“8:30”) becomes
0.354166667
• TIMEVALUE(“17:00”)-TIMEVALUE(“8:30”) becomes
0.708333333-0.354166667
Output→08:30 Similarly, use the formulas for other Exit Times and Entry Times, and finally, you will get the working hours for the employees. Related Content: How to Calculate Difference Between Two Dates and Times in Excel

### Method-4: Using TIME Function to Calculate Time Difference in Excel

You can use the TIME function for calculating the time differences between the Exit Time and the Entry Time. Step-01:
➤ Type the following formula in the cell E5

`=TIME(HOUR(D5),MINUTE(D5),SECOND(D5))-TIME(HOUR(C5),MINUTE(C5),SECOND(C5))`
• HOUR(D5)→17
• MINUTE(D5)→0
• SECOND(D5)→0
• TIME(HOUR(D5),MINUTE(D5),SECOND(D5)) becomes
TIME(17,0,0)
Output→0.708333333
• HOUR(C5)→8
• MINUTE(D5)→30
• SECOND(D5)→0
• TIME(8,30,0 becomes
TIME(17,0,0)
Output→0.354166667
• TIME(HOUR(D5),MINUTE(D5),SECOND(D5))-TIME(HOUR(C5),MINUTE(C5),SECOND(C5)) becomes
0.708333333-0.354166667
Output→08:30 ➤ Press ENTER
➤ Drag Down the Fill Handle tool Result:
Afterward, you will get the Working Hours of the employees. Related Content: How to Subtract Military Time in Excel (3 Methods)

### Method-5: Calculating Hour Differences Between Two Times of Different Dates

You can calculate the hour differences between the Delivery Time and Order Time by following this method. Step-01:
➤ Type the following formula in the cell E5

`=(D5-C5)*24`

Here, the time difference between the Delivery Time and Order Time is multiplied by 24 (1 day= 24 hours) to convert the difference into hours. ➤ Press ENTER
➤ Drag Down the Fill Handle tool Result:
In this way, you will get the hour differences between the Delivery Times and the Order Times. ### Method-6: Calculating Minute Differences Between Two Times of Different Dates

In this section, we will determine the time differences between the Delivery Times and the Order Times in minutes. Step-01:
➤ Type the following formula in the cell E5

`=(D5-C5)*1440`

Here, we have multiplied the time difference between the Delivery Time and Order Time by 1440 (1 day= 24 hours*60 minutes= 1440 minutes) to convert the difference into minutes. ➤ Press ENTER
➤ Drag Down the Fill Handle tool Result:
Then, you will get the minute differences between the Delivery Times and the Order Times. ### Method-7: Calculating Second Differences Between Two Times of Different Dates

Here, we will determine the time differences between the Delivery Times and the Order Times in seconds. Step-01:
➤ Type the following formula in the cell E5

`=(D5-C5)*86400`

Here, we have multiplied the time difference between the Delivery Time and Order Time by 86400 (1 day= 24 hours*60 minutes*60 seconds= 86400 seconds) to convert the difference into seconds. ➤ Press ENTER
➤ Drag Down the Fill Handle tool Result:
Finally, you will get the second difference between the Delivery Times and the Order Times. ### Method-8: Calculating Time Differences Using HOUR, MINUTE and SECOND Function

Here, we will use the HOUR, MINUTE, and SECOND functions to determine the time differences and split that into the hour, minute, and second units. Step-01:
➤ Type the following formula in the cell E5

`=HOUR(D5-C5)`

HOUR will return the hour value of this time difference. ➤ Press ENTER
➤ Drag Down the Fill Handle tool In this way, you will get the hour differences of the Exit Time and Entry Time. For calculating the minute differences we have used the following function

`=MINUTE(D5-C5)`

MINUTE will return the minute value of this time difference. You can use the following function to calculate the second differences

`=SECOND(D5-C5)`

SECOND will return the second value of this time difference. Note
You have to use the General format here.

### Method-9: Using NOW Function to Calculate Time Difference in Excel

To get the time difference between the current time and the Entry Time here we are using the NOW function. Step-01:
➤ Type the following formula in the cell D5

`=NOW()-C5`

NOW() will return the current time (while creating this article it was 10:54) ➤ Press ENTER
➤ Drag Down the Fill Handle tool Result:
Afterward, you will get the time difference between the current time and the Entry Time. Note
This time difference will change as the current time changes after every time you refresh or reopen your workbook.

Related Content: Timesheet Formula in Excel (5 Examples)

### Method-10: Using IF and INT Function to Calculate Time Difference in Excel

In this section, we will use the IF, INT, HOUR, MINUTE, and SECOND functions for calculating the time differences. Step-01:
➤ Type the following formula in the cell E5

`=IF(INT(D5-C5)>0, INT(D5-C5) & " days, ","") & IF(HOUR(D5-C5)>0, HOUR(D5-C5) & " hours, ","") & IF(MINUTE(D5-C5)>0, MINUTE(D5-C5) & " minutes and ","") & IF(SECOND(D5-C5)>0, SECOND(D5-C5) & " seconds","")`
• (D5-C5)→2.5
• INT(D5-C5)→2
• IF(INT(D5-C5)>0, INT(D5-C5) & ” days, “,””) becomes
IF(2>0, 2 & ” days, “,””)IF will return 2 days with the help of & operator when the difference is greater than zero, otherwise it will return a blank
Output→2 days,
• HOUR(D5-C5)→12
• IF(HOUR(D5-C5)>0, HOUR(D5-C5) & ” hours, “,””) becomes
IF(12>0, 12 & ” hours, “,””) IF will return 12 hours with the help of & operator when the difference is greater than zero, otherwise it will return a blank
Output→12 hours,
• MINUTE(D5-C5)→0
• IF(MINUTE(D5-C5)>0, MINUTE(D5-C5) & ” minutes and “,””) becomes
IF(0>0, 0 & ” minutes and “,””) IF will return 0 minutes with the help of & operator when the difference is greater than zero, otherwise it will return a blank
Output→Blank
• SECOND(D5-C5)→0
• IF(SECOND(D5-C5)>0, SECOND(D5-C5) & ” seconds”,””) becomes
IF(0>0, 0 & ” seconds and “,””) IF will return 0 seconds with the help of & operator when the difference is greater than zero, otherwise it will return a blank
Output→Blank
• IF(INT(D5-C5)>0, INT(D5-C5) & ” days, “,””) & IF(HOUR(D5-C5)>0, HOUR(D5-C5) & ” hours, “,””) & IF(MINUTE(D5-C5)>0, MINUTE(D5-C5) & ” minutes and “,””) & IF(SECOND(D5-C5)>0, SECOND(D5-C5) & ” seconds”,””) becomes
2 days,&12 hours,& “” & “”
Output→2 days, 12 hours, ➤ Press ENTER
➤ Drag Down the Fill Handle tool Result:
In this way, you will get the Time differences between the Delivery Time and the Order Time. You can get a similar result by simply subtracting the values using the following formula

`=D5-C5` and then you have to press CTRL+1 to choose the following format from the Custom option. ### Method-11: Calculating Negative Differences Between Two Times

If you want to calculate the time difference by subtracting the Entry Time and the Exit Time, then you will get a negative value due to subtracting a small value from a large value. Here, we will see how to handle this situation. Step-01:
➤ you can type the following simple formula in cell E5

`=C5-D5`

But it will not display any results So, you have to use the following formula instead

`=IF(C5-D5>0, C5-D5, TEXT(ABS(C5-D5),"-h:mm"))`
• C5-D5→-0.35416667
• TEXT(ABS(C5-D5),”-h:mm”) becomes
TEXT(ABS(-0.35416667),”-h:mm”)TEXT(0.35416667,”-h:mm”)
Output→-8:30
• IF(C5-D5>0, C5-D5, TEXT(ABS(C5-D5),”-h:mm”))becomes
IF(-0.35416667>0, C5-D5, -8:30)→As here the condition is FALSE
Output→-8:30

➤ Drag Down the Fill Handle tool Result:
Then, you will get the negative time differences. ### Method-12: Summing up the Time Values of a List

Here, we will sum up the time differences to get the total working hours. Step-01:
➤ Type the following formula in the cell E12

`=TEXT(SUM(E5:E11),"dd:hh:mm:ss")`
• SUM(E5:E11)→2.2951388889
• TEXT(SUM(E5:E11),”dd:hh:mm:ss”) becomes
TEXT(2.2951388889,”dd:hh:mm:ss”)
Output→02:07:05:00 ➤ Press ENTER

Result:
Finally, you will get the sum of the working hours where 2 is the day, 7 is the hour and 5 is the minute. ### Method-13: Adding Hours, Minutes, and Seconds

You can add up your desired hours, minutes, and seconds in the following three tables.   Step-01:
➤ Use the following formula to add up the hours with the Order Time to get the Delivery Time

`=C5+D5/24`

Here, the hour value which will be added with the Order Time is divided by 24 (1 day= 24 hours) For adding minutes use the following formula

`=C5+D5/1440`

Here, we are dividing the minute values by 1440 (1 day= 24 hours*60 minutes= 1440 minutes) We are using the following formula for adding up the seconds

`=C5+D5/86400`

So, we are dividing the second values by 86400 (1 day= 24 hours*60 minutes*60 seconds= 86400 seconds) ## Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself. ## Conclusion

In this article, I tried to cover the easiest ways to calculate the time difference in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.

## Related Articles #### Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts 