Excel 2019 Bible

Chapter 12: Using Formulas with Dates and Times

12

Inconsistent date entries

You need to be careful when entering dates by using two digits for the year. When you do

so, Excel has some rules that determine which century to use.

Two-digit years between 00 and 29 are interpreted as 21st-century dates, and two-digit

years between 30 and 99 are interpreted as 20th-century dates. For example, if you enter

12/15/28, Excel interprets your entry as December 15, 2028. But if you enter 12/15/30,

Excel sees it as December 15, 1930, because Windows uses a default boundary year of 2029.

You can keep the default as is or change it via the Windows Control Panel. From the Region

dialog box, click the Additional Settings button to display the Customize Format dialog

box. Select the Date tab and then specify a different year.

The best way to avoid any surprises is to simply enter all years using all four digits for the year.

Using Excel’s Date and Time Functions

Excel has quite a few functions that work with dates and times. These functions are acces-

sible by choosing Formulas ➪ Function Library ➪ Date & Time.

These functions leverage the fact that, beneath the covers, dates and times are nothing

more than a numbering system. This opens the door for all kinds of cool formula-driven

analyses. In this section, you’ll walk through some of these cool analyses. Along the way,

you’ll pick up a few techniques that will help you create your own formulas.

This book’s website, http://www.wiley.com/go/excel2019bible, includes a copy of the sample

workbook for this chapter. The file is named Dates and Times.xlsx.

Getting the current date and time

Instead of typing the current date and time, you can use one of two Excel functions. The

TODAY function returns the current date:

=TODAY()

The NOW() function returns the current date along with the current time:

=NOW()

Both the TODAY and NOW functions return date serial numbers that represent the current

system date and time. The TODAY function assumes 12 PM as the time, while the NOW func-

tion returns the actual time.