Date and time arithmetic has its own rules and syntax. In Excel (and in most modern computers), date and time counting begins at 12:00 AM on January 1, 1900, starting at 1. January 2, 1900 is 2, and January 1, 2014 is 41640. Time is expressed as a decimal value expressing how much of the day has passed by. For example, the serial date 41640.50 is another way of saying “noon on New Year’s Day 2014.”
Tip You can change the date or time value displayed in a cell to a serial date value by applying the General format. Click General in the Number Format list in the Number group on the Home tab. Click Short Date or Long Date to change it back again.
Fortunately, you don’t have to enter dates that way, nor do you generally need to apply date or time formats. If you enter dates and times by using standard formats, Excel recognizes them and formats them automatically. For example, if you enter 1/11/14 into a cell, Excel correctly assumes you have entered a date, and applies the equivalent of the Short Date format (on the Number Format list), displaying 1/1/2014; Excel just expands the year from 14 to 2014, so that there is no confusion about the century. But when you select the cell and look in the formula bar, Excel displays the full date and time, not a serial date value like 41640. In fact, regardless of any additional date or time formatting that you apply, the formula bar display stays the same.
This lesson is a part of Advanced formulas in Excel 2013 from 77-427 Excel 2013 Expert Part One Prep course. More lessons in this chapter are
The Practice tests included in this course are:No posts found.