Microsoft Office Tutorials and References
In Depth Information
Formatting Time
Table 13-1
How Excel Represents Time
Date and Time
Serial Format
January 1, 2014 12:00 a.m.
41640
January 1, 2014 12:01 a.m.
416409.00069
January 1, 2014 10:00 a.m.
41640.41667
January 1, 2014 12:00 p.m.
41640.5
January 1, 2014 4:30 p.m.
41640.6875
January 1, 2014 10:00 p.m.
41640.91667
January 1, 2014 11:59 p.m.
41640.99931
Time is represented in a decimal value — up to five digits to the right of the
decimal point. A value of 0 is the equivalent of 12 a.m. A value of .5 is the
equivalent of 12 p.m. — the midpoint of the day. The value of .99931 is the
same as the 23rd hour and start of the 59th minute. A value of .99999 is the
same as the 23rd hour, the 59th minute, and the 59th second — in other
words, 1 second before the start of the next day.
Can you represent time without a date? You bet! Use a value less than 1 for
this purpose. For example, the serial number 0.75 represents 6 p.m. with no
date specified.
Representing time as a serial number provides the same advantages as it
does for dates — the ability to add and subtract times. For example, given a
date/time serial number, you can create the serial number for the date/time
one and a half days later by adding 1.5 to it.
Formatting Time
When you work with time values, you’ll probably need to format cells in your
worksheet so the times display in a standard format that people will
understand. The decimal numbers don’t make sense to us human folk. To format
time, you use the Format Cells dialog box, shown in Figure 13-1. To format