Excel 2019 Bible

269

C H A P T E R

12

Using Formulas with Dates

and Times

IN THIS CHAPTER

Getting an overview of dates and times in Excel

Using Excel date-related functions

Working with Excel time-related functions

M

any worksheets contain dates and times in cells. For example, you might track informa-

tion by date or create a schedule based on time. Beginners often find that working with

dates and times in Excel can be frustrating. To work with dates and times, you need a good

understanding of how Excel handles time-based information. This chapter provides the information

you need to create powerful formulas that manipulate dates and times.

The dates in this chapter correspond to the U.S. English language date format: month/day/year. For example, the date

3/1/1952 refers to March 1, 1952, not January 3, 1952. We realize that this setup may seem illogical, but that’s the

way Americans have been trained. We trust that the non-American readers of this book can make the adjustment.

Understanding How Excel Handles Dates and Times

This section presents a quick overview of how Excel deals with dates and times. It covers Excel’s

date and time serial number system. It also provides some tips for entering and formatting dates

and times.

Understanding date serial numbers

To Excel, a date is simply a number. More precisely, a date is a serial number that represents the

number of days since the fictitious date of January 0, 1900. A serial number of 1 corresponds to

January 1, 1900; a serial number of 2 corresponds to January 2, 1900; and so on. This system makes

it possible to create formulas that perform calculations with dates. For example, you can create a

formula to calculate the number of days between two dates (just subtract one from the other).

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.