Simple date formulas Google Sheets users should learn first

Once you know the date formulas Google Sheets relies on, most date errors stop looking mysterious. The core idea is simple, because every date is a number wearing a date format. This guide covers the functions you will use weekly, then a topic that trips up many users: filtering dates inside QUERY. Every formula is ready to copy.

A date is a number with a format

Google Sheets stores dates as serial numbers, so a date is really a count of days. Google's help page for DATE says Sheets uses the 1900 date system. As a result, you can subtract one date from another and get a number of days. If A2 holds 1 March 2026 and B2 holds 15 March 2026, the formula below returns 14.

=B2-A2

Sometimes a date shows up as a plain number such as 46082, which is 1 March 2026. That means the cell has a number format, not a date format, so choose Format, then Number, then Date. The opposite problem is trickier. Text that only looks like a date stays left-aligned and breaks calculations, so convert it with DATEVALUE.

=DATEVALUE(A2)

The date formulas Google Sheets users need every week

A handful of functions cover most daily work, and the table lists each one with a sample call. TODAY returns the current date, while NOW adds the time, so pick NOW only when the time matters. DAY, MONTH, and YEAR pull one part out of a date.

DATE is the safest way to assemble a date from separate cells. The formula =DATE(C2,D2,E2) turns a year, month, and day into one real date. Keep in mind that TODAY and NOW recalculate whenever the sheet does, so a last-updated stamp built with them keeps changing. If you need a fixed stamp, type the date as a value instead.

Add months, find month ends, and count the gap

EDATE(start_date, months) returns a date that many months away, and a negative number moves backwards. So =EDATE(A2,1) gives the same day next month. EOMONTH works the same way but lands on the last day of the month, which suits invoice due dates.

For age or tenure, use DATEDIF with a unit of "Y", "M", or "D". The formula =DATEDIF(A2,TODAY(),"Y") counts complete years since the date in A2. The start date must come before the end date, otherwise you get a #NUM! error. NETWORKDAYS counts weekdays between two dates, skips Saturdays and Sundays, and accepts a holiday range as a third argument.

=EDATE(A2,1)
=EOMONTH(A2,0)
=DATEDIF(A2,TODAY(),"Y")
=NETWORKDAYS(A2,B2)

Why dates break inside QUERY

QUERY does not read a date the way a normal cell does. It expects a text literal in the form yyyy-mm-dd, introduced by the keyword date. In Ben Collins' tutorial on filtering dates in QUERY, a filter written as B > '1/1/2000' returns a blank result. Remove the quotes and you get a #VALUE! error instead.

The fix is to build the query string with TEXT. Here is the pattern for rows after the date in cell A1.

=QUERY(Data!$A$1:$H$136,"select C, B where B > date '"&TEXT(A1,"yyyy-mm-dd")&"'",1)

If you type the date inside the formula, wrap it first with TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd"). DATEVALUE turns that text into the serial number 36526, and TEXT turns the number into 2000-01-01. To filter from today, swap A1 for TODAY().

For a range between two dates, use >= on the start date so rows from that day stay in. Collins confirms in his comments that > excludes the start date, while >= includes it.

=QUERY(Data!$A$1:$H$136,"select C, B where B >= date '"&TEXT(A1,"yyyy-mm-dd")&"' and B <= date '"&TEXT(B1,"yyyy-mm-dd")&"'",1)

Two more traps are worth knowing. Inside QUERY, month() counts from zero, so month(B) + 1 = 7 matches July. Also, a timestamp with a time part can fail an equals test against a plain date, so use a >= and <= pair instead.

Run one date formula down a whole column

Dragging a formula down thousands of rows gets hard to maintain. Google added LAMBDA and helper functions in August 2022, and TechRepublic's report on LAMBDA and helper functions explains that MAP applies a LAMBDA to every item in an array. With that, one cell can fill a whole column.

=MAP(A2:A100,LAMBDA(d,IF(d="","",EDATE(d,1))))

The IF check keeps blank rows blank. TechRepublic noted at launch that some account types would not get these features, so if MAP returns an error, check your Workspace edition. For simpler cases, ARRAYFORMULA still works, and our guide on how to use array formulas in Google Sheets covers it. If you would rather describe the result than write the syntax, the Formula Foundry formula generator can draft a date formula, and you should still read it before you paste it.

Common date mistakes and quick fixes

Most broken date formulas come from the same few causes. When an error message appears, our guide to fixing Google Sheets errors covers the usual suspects. Check this list first.

• Dates stuck as text: wrap the cell in DATEVALUE, or re-enter the date in one consistent format.
• Wrong separators: in some locales, Sheets expects semicolons between arguments, so =DATE(2026;10;8) may be needed instead of commas.
• A date shown as a number such as 46082: apply a date format, because the value itself is fine.
• A time hiding in a timestamp: compare with a >= and <= pair rather than a single equals sign.
• An inclusive range that drops a day: use >= for the start date, not >.

Start with the first item whenever a date formula returns nothing at all. A single left-aligned cell in a column of dates is often the culprit, and DATEVALUE usually clears it in seconds.

Action steps

1. Check your dates are real: Look for left-aligned dates in a column, then convert any text dates with DATEVALUE.
2. Build dates with DATE: Use DATE(year, month, day) when your year, month, and day sit in separate cells.
3. Pick the right shift function: Use EDATE to move by months, EOMONTH for month ends, and DATEDIF for gaps.
4. Format QUERY dates with TEXT: Write date '"&TEXT(A1,"yyyy-mm-dd")&"' inside your query string, and use >= for the start date.
5. Fill a column in one cell: Try MAP with LAMBDA or ARRAYFORMULA so one formula covers every row.

Frequently asked questions

Why does my date show as a number in Google Sheets?

Google Sheets stores dates as serial numbers. When a cell shows a number such as 46082, it has a number format instead of a date format. Choose Format, then Number, then Date, and the same value displays as a date.

How do I add months to a date in Google Sheets?

Use EDATE(start_date, months). For example, =EDATE(A2,3) returns the date three months after the date in A2. Use a negative number to go backwards.

Why does my QUERY date filter return nothing?

QUERY expects dates as text in yyyy-mm-dd format, introduced by the keyword date. Build the filter with TEXT, for example date '"&TEXT(A1,"yyyy-mm-dd")&"', instead of typing a date like 1/1/2000 in quotes.

What is the difference between TODAY and NOW?

TODAY returns the current date only. NOW returns the current date and time. Both recalculate whenever the sheet recalculates, so neither works as a fixed timestamp.