Jump to content

How to insert the current date and time in Excel 2010

+ 1
  sarahkim's Photo
Posted Jun 30 2010 02:07 PM

Excel also provides some invaluable ready-to-use functions to help you do things like identify the current date. This excerpt from Excel 2010: The Missing Manual shows you how to use the TODAY() and NOW() functions to insert the current date and time.

The TODAY() function automatically retrieves the current date. It doesn't require any arguments because it simply examines your computer's internal clock. The TODAY() function is extremely useful for creating spreadsheets that continuously update themselves (sometimes called dynamic spreadsheets). For example, you could create a formula that determines the number of days a payment is overdue. Here's an example:

=TODAY()-A1


This formula assumes that cell A1 contains the date a payment was due. As a result, it calculates the number of days between the two dates, which shows up as an ordinary number (like 14 for a payment that's two weeks late). Remember, you'll need to display the result as an ordinary number (representing the number of days), not as a date.

The NOW() function is similar to the TODAY() function, except it retrieves the current date along with the current time. If you use NOW() to display a value in a cell that doesn't have any special formatting applied, Excel uses a custom format that shows the date and time (listed in the 24-hour format; for example, 10/5/2010 19:06).

You can use other formats to hide some of this information. For example, a cell you've formatted using the custom number format [h]:mm:ss means you'll see only the time portion 19:06, not the date information. The following formula shows one handy way to calculate the current time by completely removing the date:

=NOW()-TODAY()


Remember to format the displayed result as a time value.

Note: Excel recalculates both TODAY() and NOW() when you reopen a spreadsheet or when you explicitly refresh the worksheet by pressing F9. But, sometimes, you may want to insert the current date and make sure Excel never updates it again. In these cases, you can use the TODAY() or NOW() function, but you need to convert the result into a static date. Hit F2 to activate edit mode for the cell, and then press F9 to replace the cell with the calculated result. At this point, you'll see the serial number appear in the cell. Finally, press Enter to commit this value.

Excel 2010: The Missing Manual

Learn more about this topic from Excel 2010: The Missing Manual.

Fast-paced and easy to use, this guide shows you how to get the most out of Excel 2010. You'll learn how to develop a spreadsheet from scratch, create formulas, add data, and analyze and graph data so you can make informed business decisions. With clear jargon-free explanations, step-by-step instructions, tons of illustrations, and lots of undocumented tips and shortcuts, you'll get hands-on guided tours and explanations of Excel's new features.

See what you'll learn


Tags:
1 Subscribe


0 Replies