Site icon Professor Excel

NOW: Learn the Secrets of the Simple NOW() Formula in Excel

now, formula, function, excel

Sometimes you want to get the current time in Excel. For example within a formula: A realistic scenario could be, if you want a formula to determine whether a given time and date is in the future or the past. Or you want to insert a timestamp on your Excel sheet. Therefore, you might want to take a look at the NOW() formula.

How to use the NOW formula

The NOW formula returns the current date and time. It can be applied easily by just typing =NOW()  as shown in the picture on the right side. 

Let’s assume, you got a date in cell A1. You want to know if the date is in the future or the past. Therefore, you need to use the IF formula as follows:

=IF(A1>NOW(),”Future”,”Past”)

Please keep in mind that whenever a table is calculated, the time shown will be updated. So the formula is not able to save the last save or edit date.

Examples for the NOW() formula

Time stamp: How to insert a static date and time which doesn’t update

The problem with the NOW formula: It changes every time Excel calculates. So you can consider it always up-to-date. But sometimes you just want to get a time stamp. Here are 4 methods of how to enter a fixed date and time:

  1. Enter it manually: E.g. just type 3/27/2017 10:47 AM and press enter.
  2. Use keyboard shortcuts:
    1. Press Ctrl + ; on the keyboard for the current date.
    2. Press Ctrl + Shift + ; on the keyboard for the current date.
    3. Now you can combine these two keyboard shortcuts: First insert the current date and then the current time.
  3. The third method: Enter the NOW() formula, copy it and paste special it as values. Again, step by step:
    1. Enter =NOW() into a formula.
    2. Go to the cell in which you’ve just entered the NOW formula and copy it by pressing the keys Ctrl + c  .
    3. Paste it by pressing Ctrl + Alt + v on the keyboard. Select “Values” (press v ) and press enter .
  4. Use the following VBA macro. Therefore, insert a new module and for convenience purpose add a button which starts the macro. For more information please refer to this guide (chapters “How to insert a new VBA module manually” and “How to run your VBA macro”).
Sub updateTimeStamp()
    ActiveCell = Now()
End Sub

Difference to the TODAY formula

There is another very similar formula in Excel: the formula =TODAY() returns todays date. Let’s see the difference to =NOW():

Exit mobile version