Calculate difference in time in Microsoft Excel

Posted on:  12/19/2016
Calculate difference in time in Microsoft Excel

Let’s say that you want to find out how long it takes for an employee to complete an assembly line operation or a nurse to give insulin after a blood glucose test, or a fast food order to be processed at peak hours. There are several ways to calculate the difference between two times.

One of the easiest methods is to subtract one time from another and format the result as h:mm. When you do this your answer may show AM or PM. To correct this mistake:

  1. Simply right click the cell.
  2. Click Format Cells.
  3. Make sure you are on Custom. Click in the Type box and remove the AM/PM. See the red rectangle below.
  4. Click OK.

See the screenshot below.

Excel format cells time

Excel – YouTube video – calculate the difference in time

YouTube video below shows how to calculate the difference in time from two cells with time in them. Example: 4:25 pm – 1:18 pm. If you go over midnight (11pm to 3am), you will end up with a lot of pound symbols. Widening the column won’t help with the pound symbols. You have to write an IF Statement which is also shown in the video.

 

Use HOUR and MINUTE Function in Excel

Finally, you should know that you can use the Hour and Minute Functions in Excel.

  • =HOUR(B2-A2) will show the difference in the hours unit between two times. This value cannot exceed 24 hours.
  • =MINUTE(B2-A2) will show the difference in the minutes unit between two times. This value cannot exceed 60 minutes.

Hours and Minutes

Put in current date in Excel

  1. The current date in Excel. just use CTRL + ;
  2. The current time is CTRL + SHIFT + ;

Both are static and will not change automatically.

Put in current date in Excel and it updates automatically

To put in the current date in Excel and have it automatically update the date when you open the file use =TODAY() function.

Chris Menard

Chris Menard is a Senior Training Specialist at SurePoint Technologies. Chris is certified in Excel, Word, PowerPoint, and Outlook. Menard has a YouTube channel with other 600 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 7 million viewers have very appreciated. Because of Chris's certification and expertise with Microsoft, Chris is a proud member of Microsoft's Creator Team. Being a member of Microsoft's Creator Teams means many of his videos are available on Microsoft 365 YouTube channel and Microsoft support websites.

Categories