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.

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

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.

• 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

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

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

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

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.

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.

• 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

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.

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.

• 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

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.

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.

• 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

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

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

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.

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

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.

• 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

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.

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.

• 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

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.

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.

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

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

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

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

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

• 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