101 Ready-to-Use Excel Formulas

Chapter 4: Working with Dates and Times

In Excel, every possible date starting from January 1, 1900, is stored as a serial number. For example, January 1, 1900, is stored as 1; January 2, 1900, is stored as 2; and so on. This system of storing dates as serial numbers, commonly called the 1900 system, is the default date system for all Microsoft Office applications.

To work with times, Excel simply extends the date serial system to decimal fractions of a 24-hour day, with each time value being represented by a number between 0 and 1. Together, the date serial number and time fraction make up a date and time. For instance, the serial number 1.5 represents January 1, 1900, 12 p.m. The serial number 2.75 represents January 2, 1900, 6 p.m.

The fact that, beneath the covers, dates and times are nothing more than a numbering system opens the door for all kinds of cool formula-driven analyses. This chapter walks you through some of these cool analyses. Along the way, you pick up a few techniques that will help you create your own formulas.

You can download the files for all the formulas at www.wiley.com/go/101excelformula.

Formula 24: 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()