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.
Sam
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.
Write the headers in cells A1, B1 and C1 as Start
Time, End Time and Difference (hours and minutes) respectively
In cell A2 write a time, for example, 8:20 AM and in
cell B2 enter, for example, 9:05 AM
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
Right-click on the result cell C2 and select Format
Cells... from the menu
In the Format cell window under category select custom
Under Type select hh:mm. You could also select
hh:mm:ss to have the time difference showing the difference in seconds
Under sample you'll see the reult 0:45 indicating
correctly that the time difference between the times you entered is 45
minutes
Click OK
Next enter two more times in cells A3 and B3 and do
an autofill to calculate the time difference
However, these calculations have a great flaw if the
user forgets to enter the start time or the end time and does an
autofill
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"),"")
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
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.