Tanjima Hossain
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.
Download Workbook
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-C5It 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.
Read More: How to Calculate Time in Excel (16 Possible Ways)
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-C5→17: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)*24Here, 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.
Read More: How to Calculate Total Hours in Excel (9 Easy Methods)
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)*1440Here, 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.
Read More: How to Add Minutes to Time in Excel (5 Easy Ways)
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)*86400Here, 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.
Read More: How to Subtract Time in Excel (7 Quick Methods)
Similar Readings:
- How to Use Time Format in Excel VBA (Macro, UDF, and UserForm)
- Calculate Turnaround Time in Excel (4 Ways)
- How to Calculate Hourly Rate in Excel (2 Quick Methods)
- Calculate Total Hours Worked in a Week in Excel (Top 5 Methods)
- How to Calculate Average Response Time in Excel (4 Methods)
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.
Read More: How to Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)
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()-C5NOW() 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-D5But 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.
Read More: How to Subtract and Display Negative Time in Excel (3 Methods)
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.
Read More: [Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)
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/24Here, 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/1440Here, 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)
Read More: How to Add Minutes to Time in Excel (5 Easy Ways)
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.
Hi,
Hope you are good,
I have come across your site, and I strongly feel that your website is a perfect match for us.
I want an article to be published on your site.
Looking forward to hearing from you.
Thanks & Regards
Maureen E Claycomb
Hello, MAUREEN E CLAYCOMB! You can mention the keywords or problem here, we’ll try to prepare & publish the article within 2-3 days.