Microsoft Office Tutorials and References
In Depth Information
Chapter 15: Formatting and calculating date and time
Formatting and calculating
date and time
Understanding how Excel records dates and times ...565
Entering dates and times..........................566
Formatting dates and times .......................571
Calculating with date and time ....................576
YOU CAN use date and time values to stamp documents and to perform date and time
arithmetic. Creating a production schedule or a monthly billing system is relatively
easy with Microsoft Excel. Although Excel uses numeric values to count each
nanosecond, starting from the beginning of the twentieth century, you can use formatting to
display those numbers in whatever form you want.
Understanding how Excel records dates and times
Excel assigns serial values to days, hours, minutes, and seconds, which makes it possible for
you to perform sophisticated date and time arithmetic. The basic unit of time in Excel is
the day. Each day is represented by a serial date value. The base date, represented by the
serial value 1 , is Sunday, January 1, 1900. When you enter a date in your worksheet, Excel
records the date as a serial value that represents the number of days between the base date
and the specified date. For example, Excel represents the date January 1, 2014, by the serial
value 41640 , representing the number of days between the base date—January 1, 1900—
and January 1, 2014.
The time of day is a decimal value that represents the portion of a day that has passed since
the day began—midnight—to the specified time. Therefore, Excel represents noon by the
value 0.5 because the difference between midnight and noon is exactly half a day. Excel
represents the time/date combination 12:59:54 PM, January 1, 2014, by the serial value
41640.54159 because January 1, 2014, is day 41640 (counting January 1, 1900, as day 1),
and the interval between midnight and 12:59:54 PM amounts to .54159 of a whole day.
You can see the serial value of a formatted date by selecting the cell containing the
date and pressing Ctrl+Shift+tilde (~). Or click the Number Format drop-down list on
the Home tab and select the General format. Either way, to return the cell to its date
format, press Ctrl+Z.