Time Calculations
Time values can be entered into Excel worksheets by typing
them in the format:
hh:mm
For example 12:00 would mean mid day.
The time is based on a 24 hour clock so 1:00 would be 1 AM.
If you wish to type in 12 hour format then add the suffix AM
or PM.
It is possible to include seconds by typing a second colon
for example 12:00:10.
Time values can also be formatted afterwards in the usual
way using
Format, Cells, Number, Time. Here are a list of some of the possible ways of formatting times:
Time format How it displays hh:mm 09:00 h:mm 9:00 hh:mm AM/PM 09:00 AM, 04:00 PM hh:mm:ss 09:00:00 mm:ss.0 27:30.4 (27 mins,
30.4 seconds) [hh]:mm Allows for hour values greater than 24
When a time is entered into a cell with a valid time format
Excel actually stores the value as a number between 0 and 1. Midnight would be stored as 0 and midday as
0.5. This can be seen by choosing General from the Format, Cells number format
category,
So if a time is formatted to a date format it would appear
as 1/1/1900. As with dates, negative values are not allowed and fill the cell
with ############## marks. This causes a problem when subtracting two
times if the result is before midnight. For example: 03:00 – 4:00
In the example below the pick up time for the airport is 4
hours before the flight time.
As 4 hours is 4/24th part of a day the formula =
C2-4/24 calculates the pick up time for all guests apart from those catching
flight less than 4 hours after midnight.
To prevent an error occurring an IF statement can be used to
make the adjustment.
=IF(C2<=4/24,C2+20/24,C2-4/24)
For flights in the early hours (12-4am) instead of subtracting 4 hours the IF statement adds 20 hours.
There is an alternative and shorter formula which does away with the need for the logical function. Simply add 1 day to the time of the flight. The formula would be:
=(C2+1)-4/24
Excel now has no problem with subtracting 4 hours as the
resulting time is a positive number.
Creating Timesheets
Times can be added up with a Sum function. This goes well until the total goes above 24
hours.
|
Start |
End |
Duration |
|
09:00 |
18:30 |
09:30 |
|
09:00 |
17:00 |
08:00 |
|
08:00 |
18:00 |
10:00 |
|
|
Total |
03:30 |
To display the absolute number of hours you need to apply a
custom format:
[hh]:mm
The correct result of 27:30 is then displayed for the timesheet.
0 Comments