Excel Tip – Entering the Current Date and Time
A reminder of 2 very useful keyboard shortcuts!
Ctrl + ; Current Date
Ctrl + shift + ; Current time
These shortcuts will enter hardcoded values and will therefore not change on future days. If you dynamically need to assign the current Day and / or Time to a cell or Formula you can use the following Functions:
=Today() Current Date
=Now() Current Date and Time
NB! =Now() gives you the current Date and Time, where-as Ctrl + shift + ; only gives you the time with the Date component effectively being zero. This is important to note if you’re computing with these as the underlying values are very different.
To explain this further:
Remember that the underlying values of Dates and Times are Numeric. Days are numbered from 00-01-1900 and Time is stored as a Fraction of a Day. So, the numeric value of the 15th of Jan 2020 is 43845 and the time 14:35 will be stored as 0.6076.
When you use the Shortcut Ctrl + shift + ; there is effectively no Date component, so the Value is 0.6076. The Function =Now() will give you 43845.6076 which includes the Date Component. If you wanted to Dynamically enter the current Time without the Date component then you would need to use =Now()-Today() which using our example is effectively 43845.6076 – 43845 = 0.6076.
Working with Dates and Times can be tricky, but once you get your head around how the Values are actually stored it will open up a whole new realm of effectively automating the Date and Time components of your spreadsheets.