The NOW function is a simple date function used to return the value of the current day and time. That is all it does. However, this function helps build dynamic formulas that use the resultant date and time when used with other date functions available in Sheets.
Note that the NOW function returns date and time. If you want just the date, use the TODAY function, which operates similarly but returns the date without the time.
Contents
Video Explanation
Purpose
The NOW function returns the value of the current day and time. As explained further in how to use dates and times in Google Sheets formulas, the value is an integer for the day plus a decimal for the time but will the spreadsheet will typically display it as a date and time to the user.
Syntax
=NOW()
Note that you need the quotes even though there should be nothing in them. The sole purpose is to irritate you and make you wonder why. There is no answer. Just live with it!
Examples
Example 1 – Plain and Simple
Firstly, this is a simple example of the NOW function.
| Formula | Description | Result |
=NOW() | Outputs the current date and time (not really “now” but what today was when I wrote this!) | 1/24/2017 6:206:24 |
Example 2 – Separating the Values of a Date and Time
Different date values examined using various functions.
| Time value | Tickmark | YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | Formatted as number |
|---|---|---|---|---|---|---|---|---|
| 7/30/2016 | 1 | 2016 | 7 | 30 | 0 | 0 | 0 | 42,581.00 |
| 7-30-2016 | 2 | 2016 | 7 | 30 | 0 | 0 | 0 | 42,581.00 |
| 6/1/2017 2:32 PM | 3 | 2017 | 6 | 1 | 14 | 32 | 0 | 42,887.61 |
| July 31, 2016 | 4 | 2016 | 7 | 31 | 0 | 0 | 0 | 42,582.00 |
| 12:30 PM | 5 | 1899 | 12 | 30 | 12 | 30 | 0 | 0.52 |
| 4/11/2017 11:24:14 | 6 | 2017 | 4 | 11 | 11 | 24 | 14 | 42,836.48 |
| 4/11/2017 | 7 | 2017 | 4 | 11 | 0 | 0 | 0 | 42,836.00 |
| 8 | 1899 | 12 | 30 | 0 | 0 | 0 | ||
| 1 | 9 | 1899 | 12 | 31 | 0 | 0 | 0 | 1.00 |
| 0.25 | 10 | 1899 | 12 | 30 | 6 | 0 | 0 | 0.25 |
| 43000 | 11 | 2017 | 9 | 22 | 0 | 0 | 0 | 43,000.00 |
- 1 A random date typed in using slashes.
- 2 A random date typed in using dashes. Note that it is processed the same way as the date with slashes.
- 3 Typed a random date in and added a time. Now the function extracts the hours and minutes.
- 4 Typed in a date but spelled out. Writing out the date also works, but it won’t work if you write the day as “31st” instead of 31.
- 5 If you only type in a time, the “zero” date is 12/30/1899.
- 6 Entered with the NOW function. The output will constantly change in the linked Google Sheet.
- 7 Entered with the TODAY function. The output will change daily in the linked Google Sheet.
- 8 A blank cell is December 30, 1899. Similar to tickmark 5.
- 9 Typing a 1 will increment the value in number 10 above by 1 day. In other words, “1=12/31/1899”. It is just a matter of how it is displayed, which can be controlled by going to the Format menu and choosing Number.
- 10 Just a decimal with no whole number increments the value by hours, minutes, and seconds instead of days.
- 11 To get near the current date, you need to start with 43,000 days away from 12/30/1899.
Example 3 – Using NOW in Formulas
You may raise baby chickens in your backyard and be curious about the last time you fed them, which you use a spreadsheet to track.
Tip: Add the rounding function to display fewer decimals such as =ROUND((NOW()-(A2+B2),0)
| A | B | C | D | |
| 1 | Last feeding day | Last feeding time | Time since last feeding – Formula | Time since last feed – Result |
| 2 | 1/23/2017 | 11:25 PM | =NOW()-(A2+B2) |
0.2942467824 |
Live Examples in Sheets
Go to this spreadsheet for the examples of the NOW function shown above that you can study and use anywhere you would like.
I don’t know why but, my date is wrong now it’s 12:00 14.May but in my sheet it’s 5/13/2020 21:01:04
Check your locality the in the settings. Sheets may not know your time zone.
Hey Adam,
a short question if you don’t mind.
=IF(AND(H1>TIMEVALUE(“07:00”),H1<TIMEVALUE("19:00")),"OPEN","CLOSED")
I am using this function but it shows #ERROR!
H1 is formate in time and has the following function =now(),
What am I doing wrong?
I want the computer to analyse the time (now) and tell me if its between the two times of 7:00 and 19:00. If true, "open" and if false, "closed"
Please help me out
Wew, I’m not really sure. The only way I got it to work was to put the now function in one cell, the timevalue(“7:00”) in one cell, and the timevalue(“19:00”) in a third cell. If I referenced those, the function worked. If I embedded them, I got an error.
I have no idea why that formula does not work for Sahak but this might work instead >
=IF(and(“07:00 am”>H1,”07:00 pm”<H1),"OPEN","CLOSED")
My Cromeboook shows the correct date & time but =NOW() in Google sheets gives a tie 4 hours in the future. I have checked the settings & it is correct for my time zone. Any ideas or is this a bug?
I don’t really know on that one. I’ve never heard of that problem before!
Ok .. thanks for taking the time to consider it. I will just add minus four .. so it will be =now()-4 & hopefully that will be a good work-around.
Nope – that does not work, it reduces the days by 4 but not the hours.
This works better =now()-“4:00:00 AM” but I don’t not know if that is good for every hour of the day.
Hey! Do you know how to use the Now function for automated stock updates?
I’ve tried this…. How can I connect “now” and “Googlefiance”?
=NOW(GOOGLEFINANCE(“TICKER”))
Thanks for helping everyone out!
Hello,
to display the current hour and minutes I use this function:
=HOUR(NOW())&”:”&MINUTE(NOW())
The time should be displayed for example as: 08:01, but the leading 0 isn’t displayed I get 8:1 instead. Any solution for displaying the time with leading 0 (08:01) ?
Thanks in advance
Yeah, the TEXT function may get you where you want to do. Check that out. It let’s you customize the number of digits for certain number types including dates.
Thanks Adam,
=TEXT(NOW();”hh:mm”)
helped me