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.

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.

how to calculate time difference in excel

how to calculate time difference in excel


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.

how to calculate time difference in excel

Step-01:
➤ Type the following formula in the cell E5

=D5-C5

It will subtract the Exit Time from the Entry Time.

arithmetic operator

➤ Press ENTER
➤ Drag Down the Fill Handle tool

arithmetic operator

Result:
In this way, you will get the Working Hours of the employees.

arithmetic operator

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.

how to calculate time difference in excel

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

TEXT function

➤ Press ENTER
➤ Drag Down the Fill Handle tool

TEXT function

Result:
Then, you will get the Working Hours of the employees.

TEXT function

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 function

=TEXT(D5-C5,"hh")

You will get the difference in hours here.

how to calculate time difference in excel

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.

how to calculate time difference in excel

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

TIMEVALUE function

Similarly, use the formulas for other Exit Times and Entry Times, and finally, you will get the working hours for the employees.

TIMEVALUE function

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.

how to calculate time difference in excel

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

TIME function

➤ Press ENTER
➤ Drag Down the Fill Handle tool

TIME function

Result:
Afterward, you will get the Working Hours of the employees.

how to calculate time difference in excel

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.

how to calculate time difference in excel

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.

Hour differences

➤ Press ENTER
➤ Drag Down the Fill Handle tool

Hour differences

Result:
In this way, you will get the hour differences between the Delivery Times and the Order Times.

how to calculate time difference in excel

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.

how to calculate time difference in excel

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.

Minute differences

➤ Press ENTER
➤ Drag Down the Fill Handle tool

Minute differences

Result:
Then, you will get the minute differences between the Delivery Times and the Order Times.

Minute differences

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.

how to calculate time difference in excel

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.

Second differences

➤ Press ENTER
➤ Drag Down the Fill Handle tool

Second differences

Result:
Finally, you will get the second difference between the Delivery Times and the Order Times.

how to calculate time difference in excel

Read More: How to Subtract Time in Excel (7 Quick Methods)


Similar Readings:


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.

how to calculate time difference in excel

Step-01:
➤ Type the following formula in the cell E5

=HOUR(D5-C5)

HOUR will return the hour value of this time difference.

HOUR,MINUTE and SECOND functions

➤ Press ENTER
➤ Drag Down the Fill Handle tool

HOUR,MINUTE and SECOND functions

In this way, you will get the hour differences of the Exit Time and Entry Time.

HOUR,MINUTE and SECOND functions

For calculating the minute differences we have used the following function

=MINUTE(D5-C5)

MINUTE will return the minute value of this time difference.

HOUR,MINUTE and SECOND functions

You can use the following function to calculate the second differences

=SECOND(D5-C5)

SECOND will return the second value of this time difference.

how to calculate time difference in excel

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.

how to calculate time difference in excel

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)

NOW function

➤ Press ENTER
➤ Drag Down the Fill Handle tool

NOW function

Result:
Afterward, you will get the time difference between the current time and the Entry Time.

NOW function

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.

how to calculate time difference in excel

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,

IF and INT function

➤ Press ENTER
➤ Drag Down the Fill Handle tool

IF and INT function

Result:
In this way, you will get the Time differences between the Delivery Time and the Order Time.

IF and INT function

You can get a similar result by simply subtracting the values using the following formula

=D5-C5

IF and INT function

and then you have to press CTRL+1 to choose the following format from the Custom option.

IF and INT function


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.

how to calculate time difference in excel

Step-01:
➤ you can type the following simple formula in cell E5

=C5-D5

But it will not display any results

negative difference

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

negative difference

Result:
Then, you will get the negative time differences.

how to calculate time difference in excel

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.

how to calculate time difference in excel

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

summing up differences

➤ 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.

summing up differences

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.

how to calculate time difference in excel

addition of hour, minute, second

addition of hour, minute, second

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)

addition of hour, minute, second

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)

addition of hour, minute, second

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)

how to calculate time difference in excel

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.

practice


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

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.

2 Comments
  1. Reply
    Maureen E Claycomb Aug 15, 2022 at 3:28 PM

    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.

Leave a reply

ExcelDemy
Logo