How to Calculate Date & Time Differences in Excel

By Lea Miller

Microsoft's Excel spreadsheet program is a versatile method for managing and displaying numeric information. Another feature of Excel allows you to compare date and time sequences to know the elapsed time between two events or a series of events. Depending on how you format the result of a date comparison, you can view the number of days, months or years between dates. When comparing times, you can format the result as hours, hours and minutes, or hours, minutes and seconds.

Determine a Difference Between Dates

Step 1

Highlight two cells to be compared, A2 and B2, for example. Make sure the cells have been formatted as dates by right-clicking and selecting "Format Cells." Choose "Date" from the category menu. Select the date format you wish to use, and click "OK" to save your selection.

Step 2

Click on a cell where you want the result of the calculation for the two dates to display. Enter the formula to subtract the later date from the earlier date by subtracting one cell address from the other cell address, e.g. =A2-B2, where A2 is the later date and B2 is the earlier date. The result will be displayed as a number of days.

Step 3

Change the display result to show only the number of months between dates within the same year by using the formula =MONTH(A2)-MONTH(B2). Change the display result to show only the number of months between dates more than a year apart by using the formula =(YEAR(A2)-YEAR(B2))*12+MONTH(A2)-MONTH(B2).

Step 4

Change the display result to show only the number of years between dates by using the formula =YEAR(A2)-YEAR(B2).

Determine a Difference Between Times

Step 1

Highlight two cells to be compared, A2 and B2, for example. Make sure the cells have been formatted as times by right-clicking and selecting "Format Cells." Choose "Custom" from the category menu. Select the time format you wish to use, and click "OK" to save your selection.

Step 2

Click on a cell where you want the result of the calculation for the two times. Enter the formula to subtract the later time from the earlier time by subtracting one cell address from the other cell address, using the "TEXT" function to choose how the result will display. Use the formula =TEXT(A2-B2,"h") to display the result in hours, where A2 is the later time and B2 is the earlier time.

Step 3

Enter the formula =TEXT(A2-B2,"h:mm") to display the result in hours and minutes and the formula =TEXT(A2-B2,"h:mm:ss") to display the result in hours, minutes and seconds.

Tips & Warnings

  • If you format a cell using the "Format Cells" utility and then use a formula including the "TEXT" function, the "TEXT" function will override the other formatting.