Calculating Time Differences in MS Excel

Home More Videos

I have a question regarding time in Microsoft Excel. In one cell, let's say A2, I input a start time and in cell B2 I have the end time. Is there a formula in Excel that I can use to autofill the difference of the two times? Thanks.
Time difference calculations can be important and useful in a variety of situations like when you wish to calculate the number of hours worked or when you do an experiment where you have the start time of a chemical or biological reaction and the end time. Sam has a similar problem. Calculating time differences in Excel are easy and we describe two methods. The first method is the standard formatting route. The second method does a kind of validation where we check whether the user has entered both the start and end times. This helps to avoid useless calculations in time differences.
  1. Write the headers in cells A1, B1 and C1 as Start Time, End Time and Difference (hours and minutes) respectively
  2. In cell A2 write a time, for example, 8:20 AM and in cell B2 enter, for example, 9:05 AM
  3. In cell C2 if  we enter the formula '=B2-A2' and press enter we get 12:45 AM.! We definitely don't want this result because it is truly nonsensical
  4. Right-click on the result cell C2 and select Format Cells... from the menu
  5. In the Format cell window under category select custom
  6. Under Type select hh:mm. You could also select hh:mm:ss to have the time difference showing the difference in seconds
  7. Under sample you'll see the reult 0:45 indicating correctly that the time difference between the times you entered is 45 minutes
  8. Click OK
  9. Next enter two more times in cells A3 and B3 and do an autofill to calculate the time difference
  10. However, these calculations have a great flaw if the user forgets to enter the start time or the end time and does an autofill
  11. So we'll use a better strategy to calculate the time difference using the IF function combined with AND
  12. We'll do a kind of validation where we first check whether the user has entered the two times and then alone perform the time difference calculation by using the following formula '=If(AND(A2<>"", B2<>""),TEXT(B2-A2,"HH:MM"),"")
  13. the formula is read as follows: If the cell A2 is not bland and the cell B2 is also not blank then calculate the difference between B2 and A2 and format the result in hours and minutes else leave the result Excel worksheet cell blank
  14. This formula as you can immediately note has a major advantage: If the user forgets to enter any of the start or end times the autofill keeps the result cell blank

You can also view other time functions in MS Excel in one of our earliest videos (no sound).

Watch the Excel training video to see how the two methods of calculating time difference are implemented.

Watch the video on youtube