NOW Function - Google Sheets

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 is useful in building dynamic formulas that use the resultant date and time when used together 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 in the same way but returns just the date.

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 actually an integer for the day plus a decimal for the time but will typically be displayed 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 of this is to irritate you and make you wonder why. There is no answer, just live with it!

Examples

Example 1 – Plain and Simple

A simple example of the NOW function.

Formula	Description	Result
`=NOW()`	Output the current date and time (not really “now” but what today was when this was written!)	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 notice the hours and minutes being extracted.
4 Typed in a date but spelled out. This also works, but it won't work if you write the day as "31st" instead if 31.
5 If you only type in a time, the "zero" date is 12/30/1899.
6 Entered with the NOW function. This will change constantly in the this linked Gooogle Sheet.
7 Entered with the TODAY function. This will change daily in the this linked Gooogle Sheet.
8 A blank cell is December 30, 1899. Similar to tickmark 5.
9 Typing a 1 increments the value in number 8 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 present date, you need to start with 43,000 days away from 12/30/1899.

Example 3 – Using NOW in formulas

You may be raising baby cheetahs in your front yard and be curious of the last time that you fed them which of course you use a spreadsheet to track.

Tip: Add the rounding function to display less decimals such as =ROUND((NOW()-(A2+B2),0)

This cell would be more meaningful if it were formatted using Format -> Number -> Time.
	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.

0 thoughts on “NOW Function – Google Sheets”

Хулан Энхбаяр says:

May 14, 2020 at 12:01 am

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

Adam Steinfurth says:

May 22, 2020 at 9:31 am

Check your locality the in the settings. Sheets may not know your time zone.

Reply

Sahak says:

June 17, 2020 at 3:56 pm

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

Adam Steinfurth says:

June 18, 2020 at 3:11 am

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.

Reply