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.

Featured Posts
Posts Are Coming Soon
Stay tuned...
Recent Posts
Archive
Search By Tags

Training Offices:

Johannesburg

106 Johan Avenue, Sandton

Durban

1 Tamarind Close, Umhlanga

Pietermaritzburg

400 Old Howick Road, Hilton

Corporate Onsite Training Available Countrywide

  • Summit Solutions Excel Training
  • Summit Solutions Excel Training
  • Summit Solutions Excel Training

Tel: 086 167 3923

Email: info@summitsolutions.co.za 

Head Office: Hilton Quarry Office Park, 400 Old Howick Road, Hilton.

2020 | Copyright | Summit Solutions