How to Calculate Time Difference Between AM and PM in Excel (6 Ways)

Get FREE Advanced Excel Exercises with Solutions!

There are several functions in Excel that deal with time. However, there is no special function for calculating the difference between two times variables. While you may know, a time value is a numeric value that is represented as time, subtracting one value from another produces a decimal number, which must be structured as time again. In this tutorial, we’ll show you how to calculate the time difference between AM and PM in Excel by applying formulas.


How to Calculate Time Difference Between AM and PM in Excel: 6 Ways

We’ll go through six different approaches to compute the time difference in the sections below. We estimated the time difference in a variety of units, including hours, minutes, seconds, and also for negative values. A sample Data set is provided to do the task.

1. Use Formula to Calculate Time Difference Between AM and PM in Hours and Minutes

Follow the instructions below to determine the time difference in hours and minutes.

Step 1:

  • To subtract the difference from end time to start time, type the following formula in cell D5.
=C5-B5

Use Formula to Calculate Time Difference Between AM and PM in Hours and Minutes

Step 2:

  • Press Enter.

Use Formula to Calculate Time Difference Between AM and PM in Hours and Minutes

Step 3:

  • AutoFill the rest cells with the same formula and get the final results.

Use Formula to Calculate Time Difference Between AM and PM in Hours and Minutes

Notes.  Look carefully that the difference is shown in AM/PM units. But, if you want to show it just the time difference with hours/minutes, you need to change the format. To change the format follow the procedure.

Step 1:

  • Press  Ctrl  +  1  to open the Format cells dialog box.
  • Click on the Time option and select the Format as shown in the image below.
  • Finally, click on OK.

Use Formula to Calculate Time Difference Between AM and PM in Hours and Minutes

  • Therefore, the time difference will be shown in hh:mm.

Use Formula to Calculate Time Difference Between AM and PM in Hours and Minutes

Read More: How to Calculate Difference Between Two Dates and Times in Excel


2. Calculate the Time Difference Between AM and PM in Hours in Numbers by Applying Formula

The time difference is usually formatted as the Time format. We can’t calculate the difference in hours in decimal numbers in this format. As a result, you must change the format to the Number format. Follow these easy steps to complete the task.

Step 1:

  • Firstly, type the formula in cell D4.
=(C5-B5)*24

Calculate Time Difference Between AM and PM in Hours in Numbers Applying Formula

Step 2:

  • From the Number tab, select the Number.

Calculate Time Difference Between AM and PM in Hours in Numbers Applying Formula

Step 3:

  • Therefore, you will find the hours will be changed to decimal numbers.

Calculate Time Difference Between AM and PM in Hours in Numbers Applying Formula

Step 4:

  • Repeat the procedure for all cells in the column.

Calculate Time Difference Between AM and PM in Hours in Numbers Applying Formula

Read More: Excel Calculate Hours Between Two Times After Midnight


3. Apply the INT function to Calculate the Time Difference Between AM and PM in Completed Hours

For counting the time difference in a marginal figure we will apply the INT function with the previous method. Follow the steps outlined below to get it done.

Step 1:

  • Firstly, enter the following formula.
=INT((C5-B5)*24)

Apply the INT function to Calculate Time Difference Between AM and PM in Completed Hours

Step 2:

  • Then, press the Enter.

Apply the INT function to Calculate Time Difference Between AM and PM in Completed Hours

Step 3:

  • Apply the AutoFill handle tool to fill up the required cells.

Apply the INT function to Calculate Time Difference Between AM and PM in Completed Hours


4. Use a Formula to Calculate the Time Difference Between AM and PM in Minutes

We need to multiply the hour by 1440 to get the time difference in minutes. Follow the steps outlined below to see how we did it.

Step 1:

  • To calculate in minutes, write the formula in the formula bar.
=(C5-B5)*1440

Use Formula to Calculate Time Difference Between AM and PM in Minutes

Step 2:

  • Then, press Enter to the first result.

Use Formula to Calculate Time Difference Between AM and PM in Minutes

Step 3:

  • Finally, fill up the required cells by applying the same.

Use Formula to Calculate Time Difference Between AM and PM in Minutes

Notes.  Remember that the tome Format has to be in the Number format.

Read More: Calculate Elapsed Time Between Two Dates in Excel


5. Apply Formula to Get Time Difference Between AM and PM in Seconds

Additionally. we have to multiply the hours by 86400 to get the hours minutes. Follow the instructions below to get the answers in the unit of seconds.

Step 1:

  • At first, type the formula in cell D5.
=(C5-B5)*86400

Use Formula

Step 2:

  • Then, to see the result press Enter.

Use Formula

Step 3:

  • Therefore, repeat the steps again to fill up the required cells.

Use Formula


6. Apply the IF Function to Calculate the Time Difference Between AM and PM for a Negative Value

Importantly, the previous formulas won’t work if the end time is less than the start time. So, we will apply the conditional function IF. Follow the steps to know how to use it.

Step 1:

  • First of all, write the following formula.
=IF(C5>B5,C5-B5, (1-B5+C5))

Use Formula

Step 2:

  • Secondly, press Enter to see the value.

Use Formula

Step 3:

  • Finally, fill up the cells you need.

Use Formula

Notes.   Adding 1 means to add 24 hours in a decimal point. By adding 1, we convert the negative with a positive interval.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

To recap, I hope this tutorial has given you a solid understanding of how to calculate the time difference between AM and PM in Excel by applying formulas. All of these techniques should be learned and used on your data. Examine the practice book and put your newfound knowledge to use. Because of your vital support, we’re motivated to continue developing classes like these.

Please do not hesitate to contact us if you have any questions. Please let us know what you think in the comments area below.

Stay with us and keep learning.


Related Articles


<< Go Back to Calculate Time Difference | Calculate Time | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo