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 either 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, the date/time does NOT 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.
Dates, time and duration, the truth in Excel
Beware the date calculation trap in Excel
Converting Text with month and year into Excel dates
Five ways to turn text into Excel dates
Text to Excel Date conversion by adding Zero with Paste Special
