Entering latest date and time into Excel


There are many ways to enter the latest date or time into Excel, depending on exactly what you need.  A fixed date/time at the moment you type into the cell or a date/time which Excel updates automatically.

This works on Excel for Windows or Excel for Mac.  There are combinations to enter date, time or date and time.

If you want to skip ahead, here’s a summary of the options:

Fixed or Static date/time

There are long-standing shortcuts to type in the current date/time.  They put into the Excel cell the date/time as you type it.  Once typed, they don’t change.

It’s useful for ‘live’ data entry when you’re updating a worksheet with the today’s sale figures, attendance or whatever.

Excel enters the current Excel date/time value (ie counting from 1900) see THE TRUTH BEHIND EXCEL DATES, TIME AND DURATION and the table above.

Windows shortcuts:

Ctrl + ;   insert current date

Ctrl + Shift + ; insert current time

You can combine the two to insert both date and time:

Ctrl + ; then a space, Ctrl + Shift + ;   the space is required.

Mac shortcuts:

As with most Office for Mac shortcuts, they are the same as Office for Windows except you use the Command key ⌘ instead of Control.

Command ⌘ + ;   insert current date

Command ⌘ + Shift + ; insert current time

You can combine the two to insert both date and time:

Command ⌘ + ; then a space, Command ⌘ + Shift + ;   the space is required.

Dynamic Date/Time

Other times you want Excel to use the date or time right now.  It’s a dynamic date/time that changes every time Excel refreshes the worksheet.

Again, there are three options. Date, Time or Date and Time.  There are Excel functions for two of these and a simple formula to create the third.

Each time the worksheet refreshes, it changes the current Excel date/time value (ie counting from 1900) see THE TRUTH BEHIND EXCEL DATES, TIME AND DURATION and the table above.

Today() = Date only

The Today() function returns the current date only.

It inserts the current date as an Excel date value, how it appears in the cell depends on the cell formatting or Windows regional setting.  See the ‘Real Cell Value’ in the above table to see Today() as both a date and it’s underlying cell value.

Now() = Date and Time

The Now() function returns both date and time.

In other words, it enters an Excel date value with both numbers (day) and fraction (time) component. See the ‘Real Cell Value’ in the above table to see Now() as both a date/time and it’s underlying cell value.

Now()-Today()  = Time only

There’s no Excel function to show just the current time, without the date. But it’s easy enough to do:

=Now()-Today()  returns the time only.  In other words, it subtracts the current date/time (Excel date with fraction) from the current date only (Excel date, no fraction).  Have a look at the table above.

Static and Dynamic dates combined

A worksheet can tell you the difference between the time you entered some information (using the Date/Time shortcuts) and the current time.   Excel can do it down to the second!

Subtract the current time (Now()) from the original date/time then format the result as a time duration.


Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.