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

**Table of Contents**hide

## Download to Practice

## 17 Ways to Calculate Time in Excel

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.

`=D4-C4`

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.

**Read More:** **How to Calculate Hours Worked Minus Lunch with Excel Formula**

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

`=(D4-C4)*24`

Here, I selected **D4 **and **C4 **cells and 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.

`=INT((D4-C4)*24)`

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-

`=(D4-C4)*24*60`

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

`=(D4-C4)*24*60*60`

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

`=TEXT(D4-C4,"hh")`

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

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

`=NOW()-C4`

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.

`=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) - C4`

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.

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

`=TEXT(NOW()-C4,"dd hh:mm:ss")`

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.

`=INT(D4-C4) & " days, " & HOUR(D4-C4) & " hours, " & MINUTE(D4-C4) & " minutes and " & SECOND(D4-C4) & " seconds"`

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.

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

`=IF((D4-C4)<0,1-(D4-C4),(D4-C4))`

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.

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

`=IF(D4-C4>0, D4-C4, TEXT(ABS(D4-C4),"-h:mm"))`

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.

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

`=C4 + TIME(8, 0, 0)`

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.

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

`=C4+ (48/24)`

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.

`=C4 + TIME(0, D4, 0)`

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.

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

`=C4+ (480/1440)`

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.

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

`=C4 + TIME(0, 0, D4)`

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.

`=C4+ (320/86400)`

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.

`=C4 - TIME(D4, 0, 0)`

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

`=C4 - (48/24)`

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.

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

`=C4 - TIME(0, D4, 0)`

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

`=C4- (480/1440)`

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.

`=C4 - TIME(0, 0, D4)`

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

`=C4 -(488/86400)`

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.

`=SUM(C4:C8)`

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

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.

`=TEXT(SUM(C4:C8),"[h]:mm:ss")`

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.

`=TEXT(SUM(C4:C8),"dd hh:mm:ss")`

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.

`=MOD(D4-C4,1)*24`

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

**Read More:** **Excel Formula To Calculate Time Worked**

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

`=IF((D4-C4)*24>$H$4,$H$4,(D4-C4)*24)`

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.

`=IF((D4-C4)*24>$H$4,(D4-C4)*24-$H$4,0)`

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

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

`=(D4-C4)*24`

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.

`=IF(SUM($E$4:E4)>$I$4,SUM($E$4:E4)-$I$4,0)`

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.

`=MAX(E4-G4,0)`

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.

**Read More:** **Timesheet Formula in Excel (5 Examples)**

## [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.

## Calculate Time in Excel: Knowledge Hub

**Calculate Total Time in Excel****Calculate the Duration of Time****Sum Time in Excel****Calculate Time Difference****Calculate Time Difference Between AM and PM****Calculate Difference Between Two Dates and Times****Calculate Elapsed Time****Calculate Elapsed Time Between Two Dates****Subtract Date and Time****Calculate Hours from Date and Time****Calculate Hours Between Two Dates and Times Excluding Weekends****Calculate Total Hours****Calculate Hours Between Two Times****Calculate Hours and Minutes****Calculate Hours and Minutes for Payroll****Calculate Hours Between Two Times After Midnight****Calculate Turnaround Time****Calculate Average Turnaround Time****Calculate Turnaround Time Excluding Weekends****Calculate Lag Time****Calculate Lead Time****Calculate Slack Time****Calculate Average Response Time****Calculate Average Handling Time****Calculate Median Follow-up Time****Calculate Cycle Time****Create an Injection Molding Cycle Time Calculator****Calculate Hourly Rate****Calculate Production per Hour****Excel Formula To Calculate Time Worked****Calculate Total Hours Worked in a Week****Man Hours Calculation****Calculate On Time Delivery Performance****Calculate Travel Time Between Two Cities****Calculate Percentage of Time****Calculate Time Weighted Return****Use Time Format in Excel VBA****[Fixed!] SUM Not Working with Time Values****Calculate Billable Hours**

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