Determining the the elapsed time between two timestamps in Excel

If you have an Excel workbook containing two cells that contain a date and time and you want to know the time difference between them in days and hours, you can subtract one from the other and get the elapsed time between the two timestamps in days and hours by using a custom date and time format for the cell that will hold the results. E.g., suppose I have a spreadsheet with the following timestamps in columns A and B:

Excel Shortcuts and Productivity Hacks 2.0
Excel Shortcuts and Productivity Hacks 2.0
1x1px

  A B C
1 Start Time End Time Elapsed Time
2 1/1/18 0:01 3/1/18 15:03  
3 2/6/18 15:18 2/7/18 18:07  
4 3/1/18 7:55 3/1/18 13:01  

The cells containing the date and time have the custom format m/d/yy h:mm.




Excel - Format Cells

For row two, I want to calculate the amount of time that has elapsed from 1 minute into the first day of January 2018 until 3 minutes after 3 PM on March 1, 2018 (the displayed times below are in 24-hour clock format, aka "military time."

New Tricks: Easy Excel
New Tricks: Easy Excel
1x1px



Excel: Learn basic and advanced formulas quick and easy
Excel: Learn basic and advanced
formulas quick and easy
1x1px

Excel cell B2

In column C2, I can place the formula =B2-A2 to get the difference in time between cells B2 and A2, but I need to specify a custom format for the result to display the number of elapsed days and hours. I can use d "day(s)" h:mm "hour(s)" for the custom format. Excel recognizes that I want the number of days displayed for "d" and the number of hours followed by a colon and the number of minutes when I use "h:mm". I've also included text in the format specification since I want "days(s)" displayed after the number of days and "hour(s)" displayed after the number of hours and minutes.

Excel - Format Cells with days and hours

If I copy that formula down through rows 3 and 4, I would see 28 day(s) 15:02 hour(s) displayed in cell C2, 1 day(s) 2:48 hour(s) in cell C3, and 0 days(s) 5:06 hour(s) in cell C4.

Excel elapsed time