Time Calculation
Excel can work with time very easily.
Time can be entered in various different
formats and calculations performed.
There are one or two oddities, but nothing
which should put you off working with it.
See the TimeSheet example for an example.
Typing
time
When time is entered into worksheet it should be
entered with a colon between the hour and the minutes, such as 12:30, rather
than 12.30
1:30
|
12:30
|
20:15
|
22:45
|
- Excel can cope with either the 24hour system or the am/pm system.
- To use the am/pm system you must enter the am or pm after the time.
- You must leave a space between the number and the text.
1:30 AM
|
1:30 PM
|
10:15 AM
|
10:15 PM
|
Finding
the difference between two times
You can subtract two time values to find the
length of time between.
Start
|
End
|
Duration
|
|
1:30
|
2:30
|
1:00
|
=D24-C24
|
8:00
|
17:00
|
9:00
|
=D25-C25
|
8:00
AM
|
5:00
PM
|
9:00 AM
|
If the result is not shown correctly, you may
need to reformat the answer. Look at the section about formatting further in
this worksheet.
Adding
time
- You can add time to find a total time.
- This works well until the total time goes above 24 hours.
- For totals greater than 24 hours you may need to apply some special formatting.
Start
|
End
|
Duration
|
1:30
|
2:30
|
1:00
|
8:00
|
17:00
|
9:00
|
7:30
AM
|
5:45
PM
|
10:15
|
20:15
|
Formatting
time
- When time is added together the result may go beyond 24 hours.
- Usually this gives an incorrect result, as in the example below.
- To correct this error, the result needs to be formatted with a Custom format.
Example
1 : Incorrect formatting
Start
|
End
|
Duration
|
|
7:00
|
18:30
|
11:30
|
|
8:00
|
17:00
|
9:00
|
|
7:30
|
17:45
|
10:15
|
|
Total
|
6:45
|
=SUM(E49:E51)
|
Example 2 : Correct formatting
Start
|
End
|
Duration
|
|
7:00
|
18:30
|
11:30
|
|
8:00
|
17:00
|
9:00
|
|
7:30
|
17:45
|
10:15
|
|
Total
|
6:45
|
=SUM(E56:E58)
|
How
To Apply Custom Formatting
The custom format for time use a pair of square
brackets [hh] on either side of the hours indicators.
1. Click on the cell which needs the format.
2. Choose the Format menu.
3. Choose Cells.
4. Click the Number tag at the top right.
5. Choose Custom.
6. Click inside the Type: box.
7. Type [hh]:mm as the format.
8. Click OK to confirm.
No comments:
Post a Comment