How to Calculate Time in Excel (16 Possible Ways)

It is possible to calculate time using arithmetic operations along with excel functions because in Excel dates and times are stored as numbers in the back end. In this article, I’m going to explain how you can calculate time in Excel.

To make the explanation easier to understand I’m going to use a dataset of employee entry and exit time information. The dataset has 3 columns; these are Employee Name, Entry Time, and Exit Time.

Download to Practice

16 Ways to Calculate Time in Excel

1. Calculate Time Difference in Excel Using Operator

You can use the minus (-) operator to calculate the time difference in Excel.

To do the calculation,

⏩ In cell E4, type the following formula.

Here, I selected D4 and C4 cells then subtracted the C4 cell time from D4.

Now, press ENTER, and you will get the time difference.

Here, you can follow the same process, or you can use the Fill Handle to AutoFill the formula for the rest of the cells.

1.1. Change Time Format in h, h:mm and h:mm:ss format

Whenever we use an operator to get the time difference we do get the time but along with the existing format. If you want to get an only hour or hour and minutes then you change the format by using the ribbon.

Let me show you the process,

First, select the cell or cell range that you want to change the format.

➤ I selected the cell range E4:E11.

Now, open the Home tab >> from Number group >> select Number Format icon

A dialog box will appear.

⏩ From there select custom and type h

Finally, click OK.

Then, the selected cell range time will be converted into only hours.

Again, select the cell or cell range that you want to change the format.

➤ I selected the cell range F4:F11.

Now, open the Home tab >> from Number group >> select Number Format icon

A dialog box will appear.

⏩ From there select custom and type h:mm

Finally, click OK.

Then, the selected cell range time will be converted into only in hours & minutes.

Following the same process convert the time into an hour, minute & second format.

 Read More: How to Subtract and Display Negative Time in Excel (3 Methods)

2. Calculate Time Difference in Hours

Without changing the format you also can calculate the time difference in hours in Excel.

Let me show you the process,

⏩ In cell E4, type the following formula.

Here, I selected D4 and C4 cells then subtracted the C4 cell time from D4. Finally, multiplied by 24 (which is the number of hours in one day) to get only the hours.

Now, press ENTER, and you will get the time difference in hours.

To get the rest of the cell’s time difference, you can follow the same process, or you can use the Fill Handle to AutoFill the formula for the rest of the cells.

In case you want only the hour except decimal values then you can use the INT function.

⏩ In cell F4, type the following formula.

Here, in the INT function, I selected D4 and C4 cells then subtracted the C4 cell time from D4 and also multiplied it by 24 (which is the number of hours in one day).

Finally, the INT function will return only the integers values.

Now, press ENTER, and you will get the time difference in integer hours.

Here, you can follow the same process, or you can use the Fill Handle to AutoFill the formula for the rest of the cells.

If you want to calculate the time difference in minutes then you can use this formula

Also, if you want to calculate the time difference in seconds then you can use this formula

Read More: How to Calculate Difference Between Two Dates and Times in Excel

3. Using Excel TEXT Function to Calculate Time Difference in Hours

You also can use the TEXT function to calculate the time difference (hours) in Excel.

Let me show you the process,

⏩ In cell E4, type the following formula.

Here, in the TEXT function, I used D4-C4 as value and “hh” as format_text.

Then, the value will return the time difference and the format_text will format the time in hour.

Now, press ENTER, and you will get the time difference in hours.

Here, you can follow the same process, or you can use the Fill Handle to AutoFill the formula for the rest of the cells.

If you want to see more ways to calculate the time difference you can check the article regarding How to Calculate Time Difference.

Read More: Timesheet Formula in Excel (5 Examples)

4. Calculating Elapsed Time Using Excel NOW Function

If you want to calculate the elapsed time you surely can do it by using the NOW function.

To show you the process, I’m going to use the dataset given below.

⏩ In cell D4, type the following formula.

Here, in the NOW function, I subtracted cell C4 from the current date & time to get the elapsed time.

Press ENTER, and you will get the elapsed time from your selected date.

If you want, you can follow the same process, or you can use the Fill Handle to AutoFill the formula for the rest of the cells.

Read More: Calculate Elapsed Time Between Two Dates in Excel (5 Methods)

5. Calculating Elapsed Time Using TIME Function

In case your dataset contains only time values without dates, to calculate the elapsed time correctly you will need to use the TIME function.

Here, I’ve taken a dataset given below where I’ve only the times without any dates.

⏩ In cell D4, type the following formula.

Here, in the TIME function, I used HOUR(NOW() as hour, MINUTE(NOW()) as minute, and  SECOND(NOW())  as second.

In the HOUR, MINUTE, and SECOND functions I used the NOW function as serial_number to get the current time.

Finally subtracted the C4 cell value from the current time. 

Now, press ENTER, and you will get the elapsed time.

Here, you can follow the same process, or you can use the Fill Handle to AutoFill the formula for the rest of the cells.

Read More: How to Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)

6. Calculating Elapsed Time Using Excel TEXT & NOW Function

Another way to calculate elapsed time is by using both TEXT and NOW functions.

⏩ In cell D4, type the following formula.

Here, in the TEXT function, I used NOW()-C4 as value and “dd hh:mm:ss” as format_text.

Now, the value will return the elapsed time and the format_text will format the time in days, hours, minutes, and seconds.

Finally, press ENTER, and you will get the time elapsed in days, hours, minutes, and seconds format.

Now, you can follow the same process, or you can use the Fill Handle to AutoFill the formula for the rest of the cells.

7. Calculate & Show Time Difference

If you want to display the calculated time difference in days, hours, minutes, and seconds formats then you can do it by using the INT function, HOUR function, MINUTE function, and SECOND function together.

⏩ In cell D4, type the following formula.

Here, I used the INT function to get only the integer value of the time difference then concatenated the HOUR, MINUTE, and SECOND functions where I used D4-C4 as a serial_number for all the functions.

Press ENTER, and you will get the time displayed in days, hours, minutes, and seconds text.

Here, you can follow the same process, or you can use the Fill Handle to AutoFill the formula for the rest of the cells.

Read More: Calculate Hours Between Two Dates and Times in Excel Excluding Weekends

8. Dealing Negative Time

There is a possibility of getting  ###### error in Excel while dealing with negative times.

But there have some ways to show negative times properly in Excel. I’m going to show you these ways in this section.

8.1. Using IF Function to Calculate Negative Time

By using the IF function you can overcome the problem of negative time.

Let me demonstrate to you the process,

⏩ In cell E4, type the following formula.

Here, in the IF function, I used (D4-C4)<0 as logical_test, 1-(D4-C4) as value_if_true, and (D4-C4) as value_if_false.

Now, if the logical_test value becomes TRUE then the IF function will return the time difference subtracted by 1 otherwise the positive time difference will remain as it is.

Press ENTER, and you will get a positive time.

Here, you can follow the same process, or you can use the Fill Handle to AutoFill the formula for the rest of the cells.

8.2. Using IF, TEXT & ABS Function to Calculate Negative Time

In case you want to show the negative sign for negative time then you do it by using the IF function, TEXT function along with the ABS function.

Let me demonstrate to you the process,

⏩ In cell E4, type the following formula.

Here, in the IF function, I used (D4-C4)<0 as logical_test, D4-C4 as value_if_true, and TEXT(ABS(D4-C4),”-h:mm”) as value_if_false.

In the TEXT function, I used ABS(D4-C4) as value to get the absolute value and used “-h:mm” as format_text to get the time in negative when it is negative.

Now, if the logical_test value becomes true then the IF function will return the time difference otherwise the negative time difference will remain as it is.

Press ENTER, and you will get the negative time difference.

Here, you can follow the same process, or you can use the Fill Handle to AutoFill the formula for the rest of the cells.

Similar Readings

• How to Use Time Format in Excel VBA (Macro, UDF, and UserForm
• Excel formula to calculate hours worked & overtime [with template]
• Formula to Calculate Hours Worked Minus Lunch
• Calculate Total Hours in Excel (9 Easy Methods)

9. Adding Hours in Excel

You can add the time in Excel using the TIME function and using arithmetic calculations.

To do arithmetic calculations you will need to use the number of hours (24), minutes (1440), and seconds (86400) in one day

9.1. Add Time Under 24 Hours in Excel

You can add time under 24 hours using the TIME function.

⏩ In cell D4, type the following formula.

Here, in the TIME function, I used 8 as hour and used 0 as a minute & second as I want to add only hours. Then added it with the time of cell C4.

Press ENTER, and you will get added time.

Here, you can follow the same process, or you can use the Fill Handle to AutoFill the formula for the rest of the cells.

Read More: How to Add Hours to Time in Excel (8 Quick Ways)

9.2. Add Time Under or Over 24 Hours in Excel

By using the following formula you will get the time under or over 24 hours.

⏩ In cell D4, type the following formula.

Here, I divided the used hours 48 by 24 where 24 is the day then added the value with the C4 cell.

Later, used the Fill Handle to AutoFit the formula for the rest of the cells.

Read More: How to Add Time in Excel Over 24 Hours (4 ways)

10. Adding Minutes in Excel

10.1. Add Time Under 60 Minutes in Excel

Let me show you how you can add time under 60 minutes using the TIME function.

⏩ In cell E4, type the following formula.

Here, in the TIME function, I selected cell D4 as minute and used 0 as hour & second cause I want to add only minutes. Then added it with the time of cell C4.

Press ENTER, and you will get added time.

Here, you can follow the same process, or you can use the Fill Handle to AutoFill the formula for the rest of the cells.

Read More: How to Add Minutes to Time in Excel (3 Quick Methods)

10.2. Add Time Under or Over 60 Minutes in Excel

By using the following formula you will get the time under or over 60 minutes.

⏩ In cell D4, type the following formula.

Here, I divided the used minutes 480 by 1440 where 1440=24*60 which means one day. Then added the value with the C4 cell.

Later, used the Fill Handle to AutoFit the formula for the rest of the cells.

Read More: How to Add Minutes to Time in Excel (5 Easy Ways)

11. Adding Seconds in Excel

11.1. Add Time Under 60 Seconds

Let me show you how you can add time under 60 seconds using the TIME function.

⏩ In cell E4, type the following formula.

Here, in the TIME function, I used 50 as second and used 0 as hour & minute & second as I want to add only hours. Then added it with the time of cell C4.

Later, used the Fill Handle to AutoFit the formula for the rest of the cells.

11.2. Add Time Under or Over 60 Seconds

Let me show you how you can add time under or over 60 seconds using the arithmetic operation.

⏩ In cell E4, type the following formula.

Here, I divided the used minutes 320 by 86400 where 86400=24*60*60 which means one day. Then added the value with the C4 cell.

Later, used the Fill Handle to AutoFit the formula for the rest of the cells.

Read More: Excel Formula for Overtime over 40 Hours [with Free Template]

12. Subtract Hours in Excel

If you want to subtract hours, minutes, or seconds you surely can do it by using the TIME function and arithmetic operators.

12.1. Subtract Time Under 24 Hours

To subtract time under or over 24 hours using the arithmetic operation. It is the same as adding hours just you need to replace the “+” sign with “-” to subtract hours.

⏩ In cell E4, type the following formula.

Here, in the TIME function, I used 8 as an hour and used 0 as a minute & second as I want to subtract only hours. Then, subtracted it with the time of cell C4.

Later, used the Fill Handle to AutoFit the formula for the rest of the cells.

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

12.2. Subtract Time Under or Over 24 Hours

By using the following formula you will get the time under or over 24 hours.

⏩ In cell D4, type the following formula.

Here, I divided the used hours 48 by 24 where 24 is the day then subtracted the value with the C4 cell.

Later, used the Fill Handle to AutoFit the formula for the rest of the cells.

Read More: How to Subtract Date and Time in Excel (6 Easy Ways)

13. Subtract Minutes in Excel

13.1. Subtract Time Under 60 Minutes

To subtract time under 60 minutes using the TIME function. It is the same as adding minutes just you need to replace the “+” sign with “-” to subtract hours.

⏩ In cell E4, type the following formula.

Here, in the TIME function, I selected cell D4 as minute and used 0 as hour & second as I want to subtract only minutes. Then, subtracted it with the time of cell C4.

Later, used the Fill Handle to AutoFit the formula for the rest of the cells.

Related Content: How to Subtract Military Time in Excel (3 Methods)

13.2. Subtract Time Under or Over 60 Minutes

By using the following formula you will get the time under or over 60 minutes.

⏩ In cell D4, type the following formula.

Here, I divided the used minutes 480 by 1440 where 1440=24*60 which means one day. Then subtracted the value with the C4 cell.

Later, used the Fill Handle to AutoFit the formula for the rest of the cells.

Read More: Excel Formula for Overtime over 8 Hours (4 Examples)

14. Subtract Seconds in Excel

14.1. Subtract Time Under 60 Seconds

To subtract time under 60 seconds using the TIME function. It is the same as adding seconds; you just need to replace the “+” sign with “-” to subtract hours.

⏩ In cell E4, type the following formula.

Here, in the TIME function, I used 50 as second and used 0 as hour & minute & second as I want to add only hours. Then subtracted it with the time of cell C4.

Later, I used the Fill Handle to AutoFit the formula for the rest of the cells.

14.2. Subtract Time Under or Over 60 Seconds

By using the following formula you will get the time under or over 60 seconds.

⏩ In cell D4, type the following formula.

Here, I divided the used minutes 488 by 86400 where 86400=24*60*60 which means one day. Then subtracted the value with the C4 cell.

Later, used the Fill Handle to AutoFit the formula for the rest of the cells.

15. Calculate Total Time Using Excel SUM Function

You can calculate the total time by using the SUM function.

⏩ In cell F4, type the following formula.

Here, in the SUM function, I selected the cell range C4:C8 as a number1.

Press ENTER and you will get the total time.

Read More: How to Sum Time in Excel (9 Suitable Methods)

15.1. Calculate Total Minutes Using SUM

While calculating total time if you want you can get only the minutes by changing the format.

First, select the cell or cell range that you want to change the format.

➤ I selected cell F4.

Now, open the Home tab >> from Number group >> select Number Format icon

A dialog box will appear.

⏩ From there select custom and type [mm] to only get minutes.

Finally, click OK.

Then, the selected cell time will be converted into only minutes.

15.2. Calculate Total Seconds Using SUM

You also can get only the seconds by changing the format.

⏩ From there select custom and type [ss] to only get seconds.

Finally, click OK.

Then, the selected cell time will be converted into only seconds.

16. Using TEXT & SUM to Calculate Total Time

Another way of calculating total time is by using the TEXT function with the SUM function.

⏩ In cell F4, type the following formula.

Here, in the TEXT function, I used SUM(C4:C8) as value and “[h]:mm:ss” as format_text.

Now, the value will return the total time and the format_text will format the time in hour, minute, and second.

Now, press ENTER, and you will get the formatted total time.

16.1. Using TEXT & SUM to Calculate Total Day & Time

By using the TEXT function with the SUM function you can calculate the total day and time.

⏩ In cell F4, type the following formula.

Here, in the TEXT function, I used SUM(C4:C8) as value and “dd hh:mm:ss” as format_text.

Now, the value will return the total time and the format_text will format the time in day, hour, minute, and second.

Now, press ENTER, and you will get the formatted total time with the number of days

Read More: [Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)

Things to Remember

🔺 You may find (###) instead of showing you the time value the reason can be either the column is not width enough or the value is negative.

Practice Section

I’ve provided a practice sheet in the workbook to practice these explained examples.

Conclusion

In this article, I have explained 16 ways to use calculate time in Excel. I also tried to cover the when and why the errors may come frequently while calculating time. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.

Related Articles

• How to Calculate Average Handling Time in Excel (2 Easy Ways)
• Excel Formula To Calculate Time Worked
• How to Calculate Average Response Time in Excel (4 Methods)
• Adding Hours and Minutes in Excel (4 Suitable Methods)
• Calculate Turnaround Time in Excel (4 Ways)
• Excel Timesheet Formula with Lunch Break (3 Examples)
• Add 15 Minutes to Time in Excel (4 Easy Methods)