|
In a recent Excel class I taught, I had a question from one
of my students regarding time calculation.
“I would like to type the time an employee starts a
shift, then when it ends and have it calculate the number of hours worked.”
If you have ever done a formula in Excel before, this sounds
simple. Simply take the end time and subtract the start time.
Well, it is not so simple. If the start time is on the same
day as the end time (i.e., Start at midnight and end at 5:00 AM), it’s fine. But
if they started work at 9:00 PM and finished at 1:00 AM, it needs to know the
end time is on a different day. One way around it is to put the entire date in,
but my student frowned at me when I suggested that. She knew Excel well enough
to know there must be a better answer.
So I played with it for awhile and came up with the
following formula that accommodates all scenarios.
=B2-A2+IF(A2>B2,1)
That seemed to work and she was happy.
Until she tried to add all the hours together. She thought
a simple SUM formula at the bottom of the DURATION column would be all she
would need to add the number of hours that the person worked. She was confused
when Excel gave her some weird number.
Now we had to play with the cell format of the total cell.
She had the formula correct, but the format needed to be tweaked.
With my cursor on the SUM cell, I went into the Format menu
chose Cells and clicked on the Number tab. I clicked on the Custom Category on
the left and then typed in [hh]:mm on the right side.
The use of the [square brackets] told Excel to use elapsed
time, rather than time of day, which was the default, hence the weird number.
Now my student was finally satisfied and so was I, because I
learned something new about this amazing program!
|
