NOW: Learn the Secrets of the Simple NOW() Formula in 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.
Contents
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.
Hold on a second. Was this information helpful so far?
If yes: Why don't you subscribe to our monthly, free Excel newsletter? You get all this:
The best Excel tips, tricks and tutorials.
1x per month.
No spam. Promised.
Subscribe now!
In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Examples for the NOW() formula
- You want to know the weekday name in 10 days from today?
- Option 1:
=NOW()+10 and format the cell to only show the weekday. Therefore, press Ctrl
+ 1 
on the keyboard, select custom on the left side and type “ddd” for the abbreviation or “dddd” for the full name of the weekday. - Option 2: Copy and paste this formula: =CHOOSE(WEEKDAY(NOW()),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
- Option 1:
=NOW()+10 and format the cell to only show the weekday. Therefore, press Ctrl
- You want to know how much time has past since your last update? Let’s say, cell A1 contains a fixed date:
=NOW()-A1 You want to convert this into…- Whole days: =ROUNDDOWN((NOW()-A1),0)
- Hours: =(NOW()-A1)*24
- Minutes: =(NOW()-A1)*24*60
- Seconds: =(NOW()-A1)*24*60*60
- You want to know how old you are? Let’s assume, your birthday is given in cell A1.
- The easiest way (but unfortunately not very exact due to leap years with one more day): =(NOW()-A1)/365 or =(NOW()-A1)/365.25 . The second option is a little bit more exact though but also not 100%.
- Better: =YEARFRAC(A2,NOW()) .
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:
- Enter it manually: E.g. just type 3/27/2017 10:47 AM and press enter.
- Use keyboard shortcuts:
- Press Ctrl + ;
on the keyboard for the current date. - Press Ctrl + Shift
+ ; on the keyboard for the current date. - Now you can combine these two keyboard shortcuts: First insert the current date and then the current time.
- Press Ctrl
- The third method: Enter the NOW() formula, copy it and paste special it as values. Again, step by step:
- Enter =NOW() into a formula.
- Go to the cell in which you’ve just entered the NOW formula and copy it by pressing the keys Ctrl + c
. - Paste it by pressing Ctrl + Alt
+ v 
on the keyboard. Select “Values” (press v ) and press enter 
.
- 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”).
123Sub updateTimeStamp()ActiveCell = Now()End Sub1234Sub updateTimeStamp()Sheets("Sheet1").Range("A1") = Now()'Replace "Sheet1" with your sheetname and "A1" with the cell referenceEnd Sub
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
- Learn how Microsoft Excel performs calculations
- Use the simple and effective step-by-step guide to master each method
- Get to know the impact each method will have on performance
Learn more or get it on Amazon!
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():
- =NOW() returns the exact date and time. The underlying number could be something like 42423.9230527778. The number 42423 describes the day whereas the decimals contain the information about the time.
- =TODAY() only returns the whole number 42423.
- Both formulas are volatile. That means they will be calculated each time Excel calculates something – even if there are no changes.
