Today’s date in Excel

Working with dates is a key task in Excel. This tutorial will show you a few ways to get the current date in Excel 2003, Excel 2007 and Excel 2010.

The NOW() Function

One way to get the date is using the function =NOW(), which puts the current date and time in the cell as follows (it will show whatever the date and time is when you run it yourself):

NOW() Example

You can then format the cell (right click -> Format Cell, or press Ctrl+1 on a Windows PC) to display it as you like, for example as the date in the format dd/mm/yyyy.

Inserting the date into formulas or text fields

Something that can be useful to insert the current date into formulas or text fields. You can do this by typing a formula in the usual way, starting with an equals sign, and using the concatenation operator, &, which basically joins bits of text and numbers together. Remember to put any text in quotation marks. For example, ="Output [" & 30 & "]" would give you a cell which says Output [30] - not a very exciting example but you get the idea.

On our first try, the NOW() function isn’t very useful for this, for example, the formula ="Date: " & NOW() gives the following:

Date in formula not working

Fortunately, Excel offers another function, TEXT() which allows you to format the date in a nicer way. For example, you can use =TEXT(NOW(), "dd/mm/yyyy") to get the current date as 20/09/2019. Other formats work too, such as dd mmm yyyy which would show 20 Sep 2012, or just hh:mm for 10:35 (i.e. hours and minutes).

In this case, we want to combine the TEXT() and NOW() functions with any other text you want to appear in the cell: ="Date: " & TEXT(NOW(), "dd/mm/yyyy") which gives the output shown below:

Date in formula

Date Shortcuts

One downside to the NOW() function is that it always displays the current date. So in the example above, if you opened the spreadsheet the following day, it would show the date as 21/09/2012 (the current date). This may or may not be what you want.

One useful keyboard shortcut you can use is to press CTRL+; (hold CTRL and press the semicolon key) which inserts the current date (e.g. dd/mm/yyyy format, or your default format) into the currently selected cell. Or if you’re currently typing something into a cell, it will insert the date into what you’ve already written.

Another shortcut is CTRL+SHIFT+; (hold both CTRL and Shift, then press the semicolon key). This inserts the current time in hh:mm format. These shortcuts “hard-code” the dates, so they won’t change the next day if you re-open the spreadsheet.

Summary

The following table shows the various ways to insert the date, and the output which you’ll see in Excel.

Summary of inserting date

Comments