Search Not Just Numbers

Tuesday 29 April 2014

Excel Tip: Capturing the current date and time

A reminder of a couple of looming deadlines before I get into today's post:

Firstly, tomorrow is the last day to complete the poll on which version of Excel you use. Please complete the poll (at the top right of the blog) so that I can understand what readers are using.

Secondly, Thursday (May 1st) is your last chance to get Mynda Treacy's Excel Dashboards course with a 20% discount. So, take a look now if you don't want to miss out.

OK, on with today's tip...

There are two ways you might want to capture and use the current date and/or time:

  • Referring to the current date and/or time in a formula;
  • Entering the current date or time in a cell
Referring to the current date and/or time in a formula

Excel offers two functions for this purpose.

The TODAY function evaluates to today's date and is entered as follows (it has no arguments):

=TODAY()

The NOW function works similar to today, but also includes the current time.

If you want to know how Excel handles dates and times, take a look at my earlier post on the subject.

Entering the current date or time in a cell

Sometimes, however, it is not a dynamic reference to the the current date or time that you want, but to enter a fixed version of the current date or time into a cell. The following two shortcuts will allow you to do this:


Ctrl+;   this enters the current date in the active cell
Ctrl+: (i.e. Ctrl+Shift+;) enters the current time in the active cell

These are great for if you want to log, say, the start and end times of an activity as you do it.


Excel Dasboard Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

3 comments:

  1. You can apply both of the shortcuts at the same time into one cell to give you date & time in the same cell - the equivalent of =Now() but static.
    Ctrl+; first then Space then Ctrl Shift+; this would give you 29/04/2014 19:54 (system time at the time of key presses).
    You need to enter a space between shortcuts to improve readability and correct formatting. If you make the entries the other way around without a space, you get the wrong date!

    Steve

    ReplyDelete
    Replies
    1. Thanks Steve. That's an excellent extension of the functionality!

      Delete
    2. I created a Cell with =Now() and Cut and Past from it.

      Delete