How to Calculate Time in Excel (17 Possible Ways)

It is possible to calculate time in Excel using arithmetic operations along with 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.

Calculating time in Excel is of great importance as time values are used for employee time tracking, project or task scheduling, event planning, and other time-based analyses.

How to Calculate Time in Excel: 17 Ways

In this section, I will demonstrate 17 possible ways to calculate time in Excel, and to make my 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.

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 and 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 the 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 the 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 hours & minutes.

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

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 and then subtracted the C4 cell time from D4. Finally, multiply 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 integer 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:

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

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.

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, subtract 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.

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.

8. Dealing Negative Time

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

But there are 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 this 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 this 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.

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 an hour and used 0 as a minute & second as I wanted 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.

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.

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 a minute and used 0 as hour & second cause I wanted 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.

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 the value with the C4 cell.

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

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

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 wanted to subtract only hours. Then, subtract it from the time of cell C4.

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

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 Time in Excel

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 wanted to subtract only minutes. Then, subtract it from the time of cell C4.

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

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 subtract the value with the C4 cell.

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

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 subtract it from 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 subtract 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.

Press ENTER and you will get the total time.

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

17. How to Calculate Hours Worked in Excel

We can also calculate the amount of time or overtime an employee has worked. Here are 3 ideal examples-

17.1 Using Simple Formula to Calculate Total Hours Worked

Insert the formula below in cell E4 >> press Enter key >> use the Fill Handle tool >> change the number format to the Number option.

Here, we applied the MOD function to avoid returning negative numbers in case of the end time is before the start time.

17.2 Combining IF and SUM Functions to Calculate Overtime

Before calculating overtime, we have to determine the normal time an employee has worked. In cell E4, insert the following formula >> press Enter key >> drag down the Fill Handle tool.

Now, in cell F4, enter the following formula >> press Enter key >> drag down the Fill Handle icon. Don’t forget to change the number formatting to the Number option.

17.3 Calculate Hours Worked in a Weekly Timesheet

We can also calculate the total amount of time an employee has worked over a week. First, we have to calculate the total hours worked for each working day. For that, insert the following formula in cell E4 >> press Enter key >>  use the Fill Handle tool to copy the formula.

Afterward, we have to calculate the overtime value for each workday. For that, enter the following formula in cell G4 >> press Enter key >> drag down the Fill Handle icon.

Next, we can calculate the normal time using the MAX function. Enter the following formula in cell F4 >> press the Enter key >> drag down the Fill Handle tool.

Finally, we have to find the total normal time and total overtime. For that, insert the formula below in cell F9 >> press Enter key >> drag the Fill Handle tool to the right.

[Fixed] Results Showing Hash(#) Symbols Instead of Time or Date in Excel

In various instances of calculating time or date values, we get multiple Hash (#) symbols instead of the actual result. This can occur due to the column not being wide enough. In the following image, we have such a scenario.

To fix this, we have to adjust the column width. Hover your mouse pointer over the right side of the output column, the Column-resize symbol will be visible. Double-click on the right side of the output column.

This will adjust the column width and the actual outputs will be visible.

Another reason for getting Hash (#) symbols are having negative time values. In the following image, a few entry times are after the exit time ( these scenarios happen in the case of night shifts). Hence, we are getting negative and Hash symbols.

We can avoid these Hash symbols by dealing with the negative times by using the IF function.

Things to Remember

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

Practice Section

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

Download Practice Workbook

Conclusion

In this article, I have explained 16 ways to use calculate time in Excel. I also tried to cover 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.

Calculate Time in Excel: Knowledge Hub

<< Go Back to Date-Time in Excel | Learn Excel