9 Tricks to Calculate Time Difference Between AM and PM in Excel

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.

How to Calculate Time Difference Between AM and PM in Excel


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

Dataset named Weekly Timesheet of Mr. Liam Oliver.

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.

Subtracting Entry time from the Exit time t

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

Right-click on the mouse to get the Context menu.

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

Changing the time format from the Format Cells box.

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

Use of fill handle tool to fill up the rest of the Time Difference column.

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

Time difference achieved from the Entry time and Exit time in Excel.

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

Subtracting Entry time from the Exit time to calculate the time difference in hours considering AM and PM in Excel.

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.

Subtracting Entry time from the Exit time to calculate the time difference in minutes considering AM and PM in Excel.

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.

Subtracting Entry time from the Exit time in Seconds

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

Use of Excel TIMEVALUE function to measure the time difference

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

Use of Excel TIME function with HOUR, Minute, and SECOND Function

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

Right-click on the mouse to get the Context menu.

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

Changing the time format from the Format Cells box.

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

Time difference achieved from the Entry time and Exit time in Excel.

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

Use of Excel TEXT function to compute the time difference between Exit time and Entry time expressed in PM and AM respectively.

📕 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)

Use of HOUR, MINUTE, and SECOND functions separately

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.

Use of Excel HOUR function.

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.

Use of Excel MINUTE function.

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.

Use of Excel SECOND function.

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

Use of INT function to measure the time difference between AM and PM in Excel.

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

Use of IF, INT, HOUR, MINUTE, and SECOND functions to calculate the time difference between Exit time and Entry time expressed in PM and AM respectively in Excel.

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.

Right-click on the mouse to select Customize the Ribbon.

  • Thirdly, click on the Developer option and hit the OK button.

Enabling the Developer option in the Top Ribbon.

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

Getting the Visual Basic feature from the Developer tab.

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

Inserting Excel VBA code to calculate the time difference between Exit time and Entry time expressed in PM and AM respectively.

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

Time difference is achieved from the Exit time and Entry time.


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.

Use of HOUR, MINUTE and SECOND functions in Excel.

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

Enabling 1904 date system to calculate and display negative time in Excel.

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

Subtracting Entry time from the Exit time to calculate the time difference considering AM and PM in Excel and display the negative time.


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.

Use of INT, HOUR, MINUTE, and SECOND functions to calculate the time difference between the time over 24 hours in Excel.

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

Use of INT, HOUR, MINUTE, and SECOND functions to calculate the time difference between two times after midnight in Excel.


📄 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

(Visited 46 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo