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.

77-427 & 77-428-h4

Using the NOW and TODAY functions

The NOW and TODAY functions are as easy as it gets. Neither function takes a single argument; enter them, add ...
Read More

Using functions to serialize dates and times

When using date and time functions, Excel always calculates by using the precise underlying serial values. But when constructing formulas, ...
Read More

Concatenating text in formulas

-------------------------------------------------------- Concatenation is the linking of characters or strings in a specific order to #form a single string. The concatenation ...
Read More

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

Look up data by using functions

Lookup functions are #data-mining minions. You use them to address tables of data, searching for a particular value to display ...
Read More

Apply advanced date and time functions

Date and time arithmetic has its own rules and syntax. In Excel (and in most modern computers), date and time ...
Read More

The Practice tests included in this course are:

No posts found.