How to use the NOW function
What is the NOW function?
The NOW function returns the current date and time. It is a volatile function.
What is time in Excel?
Excel time value is a number equal to or larger than 0 (zero) and smaller than 1, formatted as a time value. One hour is 1/24, there are 24 hours in one day.
One minute is 1/1440, there are 1440 minutes in one day (60*24 = 1440). One second is 1/86400, there are 86400 seconds in one day (60*60*24 = 86400).
The following table shows whole hours, one hour is 1/24, 2 hours is 2/24, and so on.
0 - 12:00:00 AM
1/24 - 1:00:00 AM
2/24 - 2:00:00 AM
...
23/24 - 11:00:00 PM
24/24 - 12:00:00 AM
The time value is only the decimal part of a number, in other words, a value larger than or equal to 1 makes no difference, Excel uses only the decimal part of a number to create an Excel time value.
1.5 -> 0.5 -> 12:00:00 PM
The whole numbers represent dates in Excel. The whole number and the decimal part create a date and time value. Here is an example: 1.5 represents 1/1/1900 12:00 PM
What are dates in Excel?
Dates are stored numerically but formatted to display in human-readable date/time formats, this enables Excel to do work with dates in calculations.
For example, dates are stored as sequential serial numbers with 1 being January 1, 1900 by default. The integer part (whole number) represents the date the decimal part represents the time.
This allows dates to easily be formatted to display in many date/time formats like mm/dd/yyyy, dd/mm/yyyy and so on and still be part of calculations as long as the date is stored numerically in a cell.
What is a volatile function?
The NOW function is a volatile function, it updates each time you recalculate the worksheet.
What is the effect of volatile functions?
They may slow down your worksheet/workbook if you have many volatile functions. Use with caution, it may slow down your workbook considerably if used extensively.
When is the worksheet calculated?
Cells containing non volatile functions are only calculated once or until you force a recalculation, however, volatile functions are recalculated each time you type in a cell and press enter.
Can you stop recalculating a worksheet?
Yes, you can change a setting to manual recalculations.
- Go to tab "Formulas".
- Press with left mouse button on the "Calculation Options" button, a popup menu appears.
- Press with mouse on "Manual".
This stops the automatic recalculations.
How to force a recalculation?
Pressing F9 key will recalculate or refresh all the formulas and values in every worksheet of every workbook you have open.
Pressing Shift+F9 will only recalculate the formulas and values on the single worksheet you're currently viewing or active.
Pressing Ctrl+Alt+F9 is the quickest way to force a full recalculation of absolutely everything in all open workbooks, even if nothing has changed. It ignores whether changes were made or not and completely recomputes.
Are there more volatile functions in Excel?
Yes. OFFSET, TODAY, RAND among others.
| Function | Syntax | Description |
|---|---|---|
| OFFSET | OFFSET(reference, rows, cols) | Returns a cell offset from a reference cell. |
| TODAY | TODAY() | Returns the current date. |
| RAND | RAND() | Returns a random decimal between 0 and 1. |
| RANDARRAY | RANDARRAY([rows], [columns], [min], [max], [whole_number]) | Returns an array with random numbers. |
| RANDBETWEEN | RANDBETWEEN(bottom, top) | Returns a random whole number between bottom and top |
Note, that conditional formatting is extremely volatile or super-volatile meaning it is recalculated as you scroll through a worksheet.
NOW function example
Formula in cell B3:
NOW function Syntax
NOW()
NOW function Arguments
The NOW function has no arguments.
NOW function not working
'NOW' function examples
The following article has a formula that contains the NOW function.
This article demonstrates how to build a time zone chart and calculate times different than your time zone. The worksheet […]
Functions in 'Date and Time' category
The NOW function function is one of many functions in the 'Date and Time' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form