Mr. Jefferson is not only a successful industrialist but also a renowned petulant boss. Recently, he replaced a new Finger-Print machine to count the attendance of employees. Mr. Jaime is the In-charge who was responsible to calculate the time difference between Entry time and Exit time considering AM and PM in Excel. But unfortunately, he failed as the calculation was so inscrutable to him. So, Mr. Jefferson utterly lambasted the IT team including Mr. Jaime. Mr. Jaime languished due to severe anguish. In the meantime, Mr. Jaime was so indolent that he didnβt move himself to learn new things. But he was so lucky that he found an ingenious and placid man named Mr. Liam Oliver who helped him without any hesitation.

On the next day, when Mr. Jaime visited Mr. Jefferson carrying the revised document, he was so jubilant thinking that he finally got a perfect employee who can fix problems of his own. Therefore, Mr. Jefferson revoked the call to a technician from the software firm that he booked earlier. In addition, Mr. Jaime got a laudable entrance during the lunch hour. In this article, Mr. Liam delineates the approaches to calculate the time difference between AM and PM in Excel.

To construct the laconic approaches, we are going to expound on Simple calculation formulas to measure time difference, and the use of formulas containing functions including **TIMEVALUE**, **TIME**, **TEXT**, **HOUR**, **MINUTE**, **SECOND**, **INT**, and **IF** functions. Later, a simple application of **Excel VBA Macro** is also explained in this article.

**π Download Excel FileΒ **

To practice please download the Excel file from the link below:Β

## Learn to Calculate Time Difference Between AM and PM in Excel with These 9 Approaches

In this article, we will explore 9 different approaches to calculate the time difference between AM. and PM. in Excel. Here, we consider the **Weekly Timesheet of Mr. Liam Oliver** including **4 headings** as **Date**, **Weekday**, **Entry**, and **Exit**. The dataset is the representation of the first-week record of Mr. Liam Oliver. The dataset also has **10 rows** as well as **5 columns**. To construct the methods, we are going to learn the use of **TIME**, **TIMEVALUE**, **TEXT**, **HOUR**, **MINUTE**, **SECOND**, **INT**, and **IF** functions. The use of **VBA Macro** is also illustrated later. So, letβs get started.

**method**

### 1. Use Simple Subtraction and Time Formatting to Compute Time Difference

Using the direct subtraction method, one may face miscalculations as MS Excel supports a lot of time formats. So, by subtracting the Entry time from the Exit time, you may get an inaccurate value. But keep in mind that **Number Formatting** is important in this case. Please follow the required steps below.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- Firstly, select a blank cell i.e.
**F6**under the**Time Difference**heading to calculate the total duty hour. - Secondly, write the formula in the
**F6**cell.

=E6-D6

- Therefore obtain the time difference. But it is Expressed in
**Custom**mode. So, we need to format the time difference.

- Thirdly, select cell
**F6**and**Right-Click**on the Mouse to get a**Context Menu**list. - Fourthly, select the
**Format Cells**option from the**Context Menu**list.

- Fifthly, a box named
**Format Cells**appears. - Sixthly, select
**Time**from the**category**and**13:30**from the**Type**field. - Finally, hit on the
**OK**button.

- Now we obtain the time difference as
**9:00**(hour: minute). - Next, use the
**Fill Handle**tool to fill the rest automatically following the reference cells.

- Therefore, we obtain the time difference for each day of the 1st week of January.

**π Read More: ****6 Approaches to Subtract Time in Excel and Show Negative**

**method**

### 2. Apply Formula and Number Formatting to Calculate Time Difference Between AM and PM in Excel

Like the previous method, Using a simple formula we can convert the time difference into hours. Perhaps, Conversion into minutes or seconds is also possible without using any Excel functions. By subtracting the Entry time from the Exit time, we will be able to get the portion of a day in decimals. Later by multiplying **24**, **(24*60)**, and** (24*60*60)** with the decimal value, we will obtain the result for hours, minutes and seconds accordingly.

**Using Formula**

#### 2.1 Convert into Hours

Using a simple formula where the time difference is multiplied by 24, we will be able to ferret out the performed duty hour expressed in hours. Please follow the necessary steps.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- Initially, select a cell i.e.
**F6**below the**Time Difference**heading to calculate performed duty in hours. - Secondly, insert the following formula in the
**F6**cell.

=(E6-D6)*24

**(E6-D6)**is the representation of a portion of a day that is expressed in decimals. Multiplying by**24**, now it is expressed in an**hour**unit.- Therefore, we get the time difference which is the performed duty hour of
**9.00**in the**F6**cell. Now to calculate automatically following cell references, use the**Fill Handle**tool. Keep in Mind that,**Number format**should be**Number**.

**Using Formula**

#### 2.2 Convert into Minutes

Similarly, the conversion of hours into minutes is also possible by multiplying the extra **60**. Please check the required steps below.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- Again, select a cell i.e.
**F6**under the**Time Difference**heading to calculate performed duty in minutes. - Secondly, put the following formula in the
**F6**cell.

=(E6-D6)*24*60

**(E6-D6)**is the representation of a portion of a day that is expressed in decimals. Multiplying by**(24*60)**, now it is expressed in the**minute**unit.- Therefore, we get the time difference which is the performed duty is
**540.00**minutes in the**F6**cell. Now to calculate automatically following cell references, use the**Fill Handle**tool. Keep in Mind that the**Number format**should be selected as**Number**.

**Using Formula**

#### 2.3 Convert into Seconds

Following the previous method, the conversion of minutes or hours into seconds is also possible by multiplying **(24*60*60)** with the decimal obtained from the time difference between Entry time and Exit time. Please check the necessary steps below.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- Like previously, select a cell i.e.
**F6**under the**Time Difference**heading to calculate performed duty in seconds. - Secondly, input the following formula in the
**F6**cell.

=(E6-D6)*(24*60*60)

**(E6-D6)**is the representation of a portion of a day that is expressed in decimals. Multiplying by**(24*60*60)**, now it is expressed in the**second**unit.- Therefore, we get the time difference which is the performed duty is
**32400.00**minutes in the**F6**cell. Now to calculate automatically following cell references, use the**Fill Handle**tool. Keep in Mind that the**Number****Format**should be selected as**Number**.

**π Read More: ****5 Ways to Calculate Total Hours Worked in a Week in Excel**

**method**

### 3. Implement TIMEVALUE Function

Unlike the previous two methods, the use of **TIMEVALUE** function will guide us to get the performed duty hour. But the use of **TIMEVALUE** is a little bit sensitive. It doesnβt respond while using cell references. So, we need to write the Time text during the use of **TIMEVALUE** function. The **TIMEVALUE** function converts the time into a decimals value considering a whole day equals **1**. Therefore, **TIMEVALUE(β6:00 AMβ)** means **0.25** which is **one-fourth** of the day.

The general **syntax** of the **TIMEVALUE** function is as follows.

**=TIMEVALUE(time_text)**

Please follow the essential steps below.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- Primarily, select a cell i.e.
**F6**under the**Time Difference**heading to measure the performed duty in hours. - Secondly, insert the following formula in the
**F6**cell including the**TIMEVALUE**function.

`=(TIMEVALUE("5:00 PM") - TIMEVALUE("8:00 AM"))*24`

**π¨ Formula Breakdown **

`π`

Β Firstly, the **TIMEVALUE** function expresses the time in the **decimal** part of a day.

`π`

Β Secondly, **TIMEVALUE(β5:00 PMβ)** and **TIMEVALUE(β8:00 AMβ)** dictates the decimal value ofΒ **0.7083** and **0.3333** respectively.

`π`

Β Thirdly, by Subtracting the Entry time from the Exit time, we obtain **0.375** and by multiplying **24**, we obtain **9 hours** in the **F6** cell.

- In the end, we obtain
**9 hours**of the time difference in the**F6**cell. - Furthermore, calculate the time difference separately by putting a value for each time by assigning the
**TIMEVALUE**function.

**π Read More: ****7 Examples to Calculate Duration of Time in Excel**

**method**

### 4. Utilize TIME Function to Measure Performed Duty time in Excel

Another quick approach is the use of the **TIME** function to calculate the performed duty time. But later Formatting Cells is required once the application of the **TIME** function is done. **TIME** function gathers the value of hours, minutes, and seconds altogether.

Please check the general **syntax** of the **TIME** function below.

**=TIME(hour, minute, second)**

Please check the essential steps below.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- To begin the procedure, select the
**F6**cell under the**Time Difference**heading to compute the time difference between Entry time and Exit time. - Secondly, write down the following formula in the
**F6**cell containing the**TIME**function.

=TIME(HOUR(E6), MINUTE(E6), SECOND(E6)) β TIME(HOUR(D6), MINUTE(D6), SECOND(D6))

**π¨ Formula Breakdown**

`π`

Β Firstly, the **HOUR**, **MINUTE**, and **SECOND** functions take the hour, minute, and second respectively from the **D6** and **E6** cells.

`π`

Β Secondly, the **TIME** function considers the hour, minute, and second separately by subtracting the **D6** from the E6 cell which results in **9:00:00 AM**.

- Therefore, we obtain
**9:00:00 AM**which express in**hr: min: sec (AM/PM)**format. So next we need to select the right format to get accurate results. Auto-filling the**Time Difference**column, we obtain the time difference between Entry time and Exit time.

- Then, Select the range
**F6:F10**and**Right-Click**on the mouse to get a**Context****Menu**list. - Next, Select the
**Format Cells**option from the**Context****Menu**list.

- After that, a box named
**Format****Cells**appears. - Further, select
**Time**from the**category**and**13:30**from the**Type**field. - Finally, hit on the
**OK**button.

- Thus we obtain the time difference as
**9:00**(hour: minute) in the**F6**cell and get the result accordingly.

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

**method**

### 5. Employ TEXT Function to Measure Time Difference Between Entry Time and Exit Time in Excel

Calculation of the time difference can take place with the help of the **TEXT** function considering the Entry time and Exit time. With the help of the **TEXT** function, formatting is also possible automatically just by simply mentioning the desired format in the formula.

Please follow the general **syntax** of the **TEXT** function.

**=TEXT(Value, βFormat_codeβ)**

Please check out the required steps.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- In the beginning, select.
**F6**cell below the**Time Difference**heading to measure performed duty hour from Entry time and Exit time. - Secondly, input the following formula in the
**F6**cell containing the**TEXT**function.

`=TEXT(E6-D6, "hh:mm:ss")`

**π¨ Formula Breakdown**

`π`

Β Firstly, the **TEXT** function considers the difference obtained from **E6-D6**.

`π`

Β Then it converts the difference into **hr: min: sec** format and results in **09:00:00** in the **F6** cell.

- Lastly, we achieve
**09:00:00**in the**F6**cell formatted as**hr:min:sec**. - Furthermore, use the
**Fill Handle**tool to calculate automatically in the rest of the**Time Difference**column.

**π Read More: ****4 Examples of Timesheet Formula with Lunch Break in Excel**

**method**

### 6. Calculate Time Difference in Specific Unit (Hour or Minute or Second)

Sometimes we need to measure the time difference or the performed duty hour in a specific unit like hours, minutes, or seconds. Excel has functions like **HOUR**, **MINUTE**, and **SECOND** functions that measure just Hours, minutes, and seconds from a given time separately.

Please check out the general **syntax** of the **HOUR**, **MINUTE**, and **SECOND** functions below.

**=HOUR(Time_serial)**

**=MINUTE(Time_serial)**

**=SECOND(Time_serial)**

**Specific Unit**

#### 6.1 Express in Hour Unit Using HOUR Function

**HOUR** function limits up to **24** which means **24** hours a day. Whatever the time is it only measures the hour portion from the given time and results in hours. Please check the required steps below.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- First, navigate the
**F6**cell below the**Hour**heading to calculate the hours only. - Secondly, insert the formula in the
**F6**cell having the**HOUR**function.

=HOUR(E6-D6)

- Therefore, get the
**9 hours**of performed duty in the**F6**cell. In addition, use the**Fill Handle**tool to fill the rest of the**Hour**column following the required cell references.

**Specific Unit**

#### 6.2 Express in Minute Unit Using MINUTE Function

**MINUTE** function limits up to **60** which means **60** **minutes** an hour. Whatever the time is it only measures the minute portion of the given time and results in minutes. Please check the necessary steps.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- Again, navigate the
**G6**cell below the**Minute**heading to compute the minutes only. - Secondly, insert the formula in the
**G6**cell having the**MINUTE**function.

=MINUTE(E6-D6)

- Therefore, get the
**0 minutes**of performed duty in the**F6**cell. In addition, use the**Fill Handle**tool to fill the rest of the**Minute**column following the required cell references. - Also, you may notice that, without the
**G6**cell, we obtain minute values in the**Minute**column.

**Specific Unit**

#### 6.3 Express in Second Unit Using SECOND Function

**SECOND** function limits up to **60** which means **60** **seconds** a minute. Whatever the time is it only measures the second portion from the given time and results in seconds. Please follow the essential steps.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- Like the previous method, pick up the
**H6**cell below the**Second**heading to compute the seconds only from the time difference. - Then, input the formula in the
**H6**cell having the**SECOND**function.

=SECOND(E6-D6)

- Consequently, obtain the
**0 seconds**of performed duty in the**F6**cell where Mr. Liam performed**9 hours**on Monday. In addition, use the**Fill Handle**tool to fill the rest of the**Second**column following the cell references.

**π Read More: ****4 ways to Create Excel Formula for Overtime Over 8 Hours**

**method**

### 7. Use Excel INT Function to Compute Time Difference in Completed Hours

**INT** function is an **integer** function. It rounds down a double value. Using the **INT** value you will be able to calculate the performed duty hours within a second. Say **10.2** is a value, the **INT** function rounds down and converts the value into **10**.

Now please check the general **syntax** of the **INT **function below.

**=INT(Value)**

Please check out the required procedure.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- Initially, pick up the
**F6**cell below the**Time Difference**heading to measure performed duty hour from Entry time and Exit time. - Secondly, write down the following formula in the
**F6**cell containing the**INT**function.

=INT((E6-D6)*24)

**π¨ Formula Breakdown**

`π`

Β Firstly,** E6-D6** dictates the portion of a day considering the Entry time and Exit time.

`π`

Β Then, the multiplication of **24** converts the **decimals** into **hours**.

`π`

Β Next, the **INT** function round down the double value into the nearest **integer**.

- Finally, we achieve the time difference of
**9 hours**in the**F6**cell. - In addition, utilize the
**Fill Handle**tool to calculate the**F6:F10**range automatically.

**π Read More: ****How to Calculate Overtime and Double Time with Formula in Excel**

**method**

### 8. Apply Excel IF Function to Calculate Time Difference Between AM and PM

Using the **IF** function along with **HOUR**, **MINUTE**, **SECOND**, and **INT**Β functions can be another way to measure the time difference between the Entry time and Exit time. Calculation of performed duty hours in a week will be in your hand without any hesitation using this formula. Though this formula may be a little bit complex for you. However, we are going to discuss step by step. Letβs check it out.

Check the general **syntax** of the **IF** function as follows.

**=IF(Logical_test, if_True, if_False)**

Please follow the required steps below.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- To begin the method, select the
**F6**cell first below the**Time Difference**heading to measure performed duty hours considering the Entry time and Exit time of Mr. Liam on Monday. - Then, input the following formula in the
**F6**cell containing the**IF**,**INT**,**HOUR**,**MINUTE**, and**SECOND**functions.

`=IF(INT(E6-D6)>0, INT(E6-D6) & " days, ","") & IF(HOUR(E6-D6)>0, HOUR(E6-D6) & " hours, ","") & IF(MINUTE(E6-D6)>=0, MINUTE(E6-D6) & " minutes and ","") & IF(SECOND(E6-D6)>=0, SECOND(E6-D6) & " seconds","")`

**π¨ Formula Breakdown **

`π`

Β Firstly, **E6-D6** dictates the portion of a day considering the Entry time and Exit time.

`π`

Β Secondly, the **INT** function rounds down the value, and the **IF** function acts based on logic like ifΒ ** INT(E6-D6)** is greater than** 0** then write down its value with days which is connected by an **Ampersand** (**&**).

`π`

Β Thirdly, the **HOUR** function takes the Hour value from **(E6-D6)** and if it is greater or equal to 0 then writes its result including **hours**. It results in, **9 hours**.

`π`

Β Fourthly, the **MINUTE** function considers the Minute value from **(E6-D6)** and if it is greater or equal to 0 then writes its result including **minutes**. It results in, **0 minutes**.

`π`

Β Fifthly, the **SECOND** function undertakes the Second value from **(E6-D6)** and if it is greater or equal to 0 then writes its result including **Second**. It results in, **0 seconds**.

`π`

Β Finally, get the result is **9 hours, 0 minutes, and 0 seconds** in the **F6** cell.

- Finally, we achieve the time difference of
**9 hours, 0 minutes, and 0 seconds**in the**F6**cell. - In addition, utilize the
**Fill Handle**tool to calculate the**F6:F10**range of the**Time Difference**column automatically.

**method**

### 9. Utilize VBA Macro to Calculate Time Difference between AM and PM in Excel

Using a simple **Excel VBA** code you will be able to calculate the time difference between Entry time and Exit time expressed in AM and PM. But applying **VBA** **Macro** is not everyoneβs cup of coffee. During the use of **VBA** **Macro**, we must include various formulas and functions that measure the time difference between the Entry time and Exit time. But you might have learned that we canβt insert functions and formulas directly while using **VBA**. To learn the ingenious procedure, please check the required steps below.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- Check first if your
**Developer**option is available or not. - Secondly, to enable the
**Developer**option,**Right-Click**on the mouse at the**Top Ribbon**and select**Customize the Ribbon**.

- Thirdly, click on the
**Developer**option and hit the**OKΒ**button.

- Now
**Developer**mode is on. - Then, click on the
**Visual Basic**feature**.** - Next, open the dedicated worksheet
**VBA**or the**Module**from the**Insert**menu.

- To proceed with the method, insert the
**VBA**code in the**VBA pop-up**box and hit the**Run**icon to execute further.

__Code__

```
Sub calculate_time_difference_between_AM_PM()
Dim p As Date
Dim q As Date
Dim r As Double
For i = 1 To 5
p = Cells(i + 5, 4).Value
q = Cells(i + 5, 5).Value
r = (q - p) * 24
Cells(i + 5, 6).Value = r
Next i
End Sub
```

**π¨ Code Breakdown **

`π`

Β First, Set **p** and **q** as **date** as these are the **Entry** **time** and **Exit time** respectively.

`π`

Β Then, we must use **FOR** loop, as we are going to calculate the duty hours for 5 days in a week. So, set** i** as **Integer** and define **i = 1 to 5**.

`π`

Β Next, let us say, **p** and **q** represent the **Entry** and **Exit** column respectively.

`π`

Β After that, the calculation takes place, and store the value in the **Time difference** column.

`π`

Β Thus, the same calculation repeats **5 times** and gathers calculated data in the **F6:F10** range.

- Therefore, obtain the time difference in hours for each day in the
**F6:F10**range which is the duty hours of 1st week of 2023, performed by Mr. Liam Oliver.

## How to Format Time Difference as dd Days, hh Hours, mm Minutes and ss Seconds

Suppose you are asked to measure the time difference in hours, minutes, and seconds. Use of the combination of **HOUR**, **MINUTE**, and **SECOND** functions can be a key to computing the time difference in Excel considering Entry time and Exit time that are expressed in AM and PM in Excel.

Please follow the required steps below.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- Firstly, get the F6 cell below the
**Time Difference**heading to measure performed duty hour from Entry time and Exit time. - Secondly, type the following formula in the
**F6**cell containing the**HOUR**,**MINUTE**, and**SECOND**functions.

`= HOUR(E6-D6) & " hours, " & MINUTE(E6-D6) & " minutes and " & SECOND(E6-D6) & " seconds"`

**π¨ Formula Breakdown**

`π`

Β Firstly, **E6-D6** dictates the portion of a day considering the Entry time and Exit time.

`π`

Β Then, **HOUR(E6-D6)** means **9** and joins hours by the **Ampersand** (**&**) operator.

`π`

Β Similarly, **MINUTE** and **SECOND** functions take minutes and second values from the **(E6-D6)** which is a portion of a day.

`π`

Β Finally, the output is **9 hours, 0 minutes, and 0 seconds** in the **F6** cell.

- Therefore, we achieve the time difference of
**9 hours, 0 minutes, and 0 seconds**in the**F6**cell. Further, use the**Fill Handle**tool to measure the time difference automatically in the**F6:F10**range.

**π Read More: ****How to Calculate Turnaround Time in Excel Excluding Weekends**

## How to Calculate and Display Negative Times in Excel

**MS Excel** gives us the opportunity to calculate negative time. Enabling the **1904** **date** **system** from the **Excel Options** menu, one can calculate and display **negative** **time** within a moment.Β Please check out the pertinent steps below.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- First, get the
**Excel Options**box from the**File**menu. - Then, Select the
**Advance**option from the**Left Side Ribbon**. - Next, select and enable the
**Use 1904 date system**as it calculates dates including the**negative**sign. - Further, hit the
**OK**button to save the changes.

- Now like previously, select a cell i.e.
**F6**under the**Time****Difference**heading to calculate performed duty in**hours**. - Secondly, type the following formula in the
**F6**cell.

=(D6-E6)*24

**(D6-E6)**is the representation of a portion of a day which is expressed in decimals and minus. Multiplying by**24**, now it is expressed in an hour unit containing a**negative**sign.- Therefore, we get the time difference which is the performed duty hour of
**-9.00**in the**F6**cell. Now to measure automatically considering the cell references, use the**Fill Handle**tool.

## How to Sum Over 24 hours in Excel

Suppose Mr. Liam went for a site visit and he is bound to perform duty during the whole project for over 24 hours. In the meantime, Human Resource Officer asked about the project execution time. The use of **INT**, **HOUR** and **MINUTE**, and **SECOND** functions can be an approach to measure the time difference between the Starting Day and Ending Day.

Please follow the necessary procedure below.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- Firstly, navigate the
**D6**cell below the**Time Difference**heading to compute the project completion time. - Secondly, type the following formula in the
**D6**cell containing the**INT**,**HOUR**,**MINUTE**, and**SECOND**functions.

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

**π¨ Formula Breakdown**

`π`

Β Firstly, **C6-B6** dictates the portion of a day considering the Entry time and Exit time.

`π`

Β Secondly, the **INT** function considers the round down value and joins before days that connect with an **Ampersand**(**&**) operator. Therefore, it results in **1** day.

`π`

Β Then, **HOUR(C6-B6)** means **9** and joins hours by the **Ampersand** (**&**) operator. This results in **9 hours**.

`π`

Β Similarly, **MINUTE** and **SECOND** functions take minutes and second values from the** (C6-B6)** which is a portion of a day.

`π`

Β Finally, the output is **1 day, 9 hours, 0 minutes, and 0 seconds** in the **D6** cell.

- Lastly, achieve the time difference of
**1 day, 9 hours, 0 minutes, and 0 seconds**in the**D6**cell. Further, to compute the time difference automatically in the**D6:D8**range use the**Fill Handle**tool.

**π Read More: ****5 Easy Ways to Fix When SUM Time Is Not Working in Excel**

## How to Calculate Hours Between Two Times After Midnight in Excel

Like previously, if Mr. Liam is asked to calculate calculated hours between two times after Midnight in Excel. The combination of **INT**, **HOUR**, **MINUTE,** and **SECOND** functions is a savior in this case also.

Please follow the required steps below.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- Similar to the previous method, pick up the
**D6**cell under the**Time Difference**heading to compute the project completion time. - Then, input the following formula in the
**D6**cell including the**INT**,**HOUR**,**MINUTE**, and**SECOND**functions.

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

**π¨ Formula Breakdown**

`π`

Β Firstly, **C6-B6** dictates the portion of a day considering the Starting Day and Ending Day.

`π`

Β Secondly, the **INT** function considers the round down value and joins before days that connect with an **Ampersand **(**&**) operator. Therefore, it results in **0** **days**.

`π`

Β Then, **HOUR(C6-B6)** means **17** and joins with hours by the **Ampersand** (**&**) operator. This results in **17 hours**.

`π`

Β Next, **MINUTE** and **SECOND** functions take minutes and second values respectively from the **(C6-B6)** which is a portion of a day.

`π`

Β Finally, the output is **0 days, 17 hours, 0 minutes, and 0 seconds** in the **D6** cell.

- Consequently, get the time difference of
**0 days, 17 hours, 0 minutes, and 0 seconds**in the**D6**cell. Additionally, to compute the time difference automatically in the**D6:D8**range use and drag down the**Fill Handle**tool.

## π Important Notes

`ποΈ`

Β Enable the** Developer** menu first to execute the** VBA** code.

`ποΈ`

Debug step by step while creating a calculator using **VBA** **Macro**.

`ποΈ`

While using a simple subtraction formula and **Number Formatting**, select **Number** **Format** as **Number**. Otherwise, miscalculations will take place.

`ποΈ`

**TIMEVALUE **function doesnβt work while using multiple cell references.

`ποΈ`

**HOUR** function only catches the Hour value. In the meantime, **MINUTE** and **SECOND** functions catch minute and second data from the decimal value of a day.

## π Takeaways from This Article

`π`

Β Β Basic calculation and Number Formatting to calculate the time difference between AM and PM in Excel.

`π`

Β General mathematical calculation to express the time difference in hours, minutes and seconds separately.

`π`

Β **TIMEVALUE** function to measure the performed duty hour from the Entry time and Exit time.

`π`

Β **TIME** function to convert the time difference into the hour: minute format.

`π`

Β **TEXT** function to convert the time difference into the **hour: minute: second** format.

`π`

Β **HOUR**, **MINUTE**, and **SECOND** functions to Express the time into the **hour**, **minute**, and **second** format.

`π`

Β **IF** function to measure the time difference as well as change its format.

`π`

Β Use of **INT** function to round down to the nearest value.

`π`

Β **Excel VBA** **Macro** to calculate the time difference between Entry time and Exit time considering AM and PM.

`π`

Β Formula associated with **INT**, **HOUR**, **MINUTE**, and **SECOND** functions to compute the time difference over 24 hours.

`π`

Β Calculate the time difference between two times after midnight using Excel **INT**, **HOUR**, **MINUTE**, and **SECOND** functions.

`π`

Β Calculation of negative time enabling **1904** **date** **system** from** Excel Options** menu.

## Conclusion

In this article, we construe **9 approaches** to calculate the time difference between entry time and exit time considering AM and PM in Excel concisely. I hope you enjoyed your learning and will be able to measure the performed duty hour using MS Excel. Any suggestions including queries are appreciated. Donβt hesitate to leave your thoughts in the comment section. For better understanding and new knowledge, donβt forget to visit **www.ExcelDen.com**.

## Related Articles

**4 Easy Ways to Calculate Percentage of Time in Excel****How to Calculate Average Response Time in Excel****2 Ways to Calculate Travel Time Between Two Cities in Excel****4 Easy Methods to Calculate Production per Hour in Excel****6 Examples to Calculate Cycle Time in Excel**