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

**Table of Contents**hide

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

`=D4-C4`

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.

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

`=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, subtracted it with 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, subtracted it with 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 subtracted 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.

`=C5 - TIME(0, 0, D5)`

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.

`=C4 -(488/86400)`

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.

`=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 **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

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