Excel Cookbook

Chapter 6. Dates and Times

Dates and times are crucial to many spreadsheet calculations, yet working with them can be surprisingly complex and frustrating.

This chapter takes you through how to use formulas to solve many date and time problems, such as extracting parts of a date, calculating calendar and fiscal quarters, performing date and time calculations, and using working days. Above all, the chapter helps you understand how date/time serial numbers work, taking the mystery out of working with this type of data.

6.1 Returning the Current Date and Time

Problem

You want to enter the current date or time in a cell or use it in a formula.

Solution

If you want to enter the current date in a cell, you can use the keyboard shortcut Ctrl+; which enters a static date that doesn’t automatically update. You can also use Ctrl+Shift+; in Excel for Windows or Cmd+; in Excel for Mac to enter the current time. To enter the current date and time in a single cell in Excel for Windows, for example, you’d press Ctrl+;, then press Space, and then Ctrl+Shift+;.

If you want the date and time to dynamically update or to use them in other formulas, you can use the TODAY and NOW functions. For example, typing =TODAY() returns the current date, and typing =NOW() returns the current date and time.

Discussion

TODAY and NOW are frequently used with other formulas because they dynamically return the current date and time. Recipe 6.13, for example, uses the TODAY function to calculate someone’s current ...