Excel Tip – Entering the Current Date and Time

January 16, 2020

 

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.

Share on Facebook
Share on Twitter
Please reload

Featured Posts

Excel Tip – Copy & Paste and keep Column Widths

February 26, 2020

1/1