Dates
How dates reach the formula and how to write them back; dates in words (DATE.WHEN); adding, counting, start and end of periods; working days and public holidays by country; formats in your language.
How dates arrive and how to write them
A Date column reaches the formula as 2026-11-09, or 2026-11-09 14:30:00 when it has a time. A Timeline gives 2026-11-01 - 2026-11-30, a Week column its first and last day. To write into a Date column, the result must be YYYY-MM-DD, optionally followed by the time.
The DATE functions below (new recipes) give dates in exactly that form, so their result can go straight into a Date column. The spreadsheet functions give a date number instead (46335 is 2026-11-09), to be turned into text with TEXT(…, "YYYY-MM-DD").
TODAY() and NOW() use the Europe/Rome time zone.
A date in words new recipes
DATE.WHEN(text, [from]) reads a date described in English, as PHP’s strtotime does. Without a starting date it starts from now.
=DATE.WHEN("first monday of next month")
=DATE.WHEN("+2 weeks", {item's Start})
=DATE.WHEN("last friday of this month", {item's Date})
=DATE.WHEN("tomorrow 9:00")| You write | Means |
|---|---|
today, tomorrow, yesterday, now |
Those days (at midnight; now keeps the time) |
noon, midnight, 9:00, 5pm |
A time, alone or after a day: tomorrow noon |
monday, next monday, last friday |
The coming Monday (today if it’s Monday), the Monday after today, the Friday before today |
monday next week, next week |
The Monday of next week, or another day of it |
+3 days, -1 month, +1 week 2 days, 2 days ago |
Moved by days, weeks, months, years, hours, minutes |
+5 weekdays, next weekday |
Moved by working days (Monday to Friday) |
first day of next month, last day of this month |
The first or last day of a month |
second tuesday of next month, last sunday of march 2027 |
A weekday of a month |
2027-01-15, 15 january 2027, Dec 25 |
A date (without a year, the year of the starting date) |
A text it can’t read gives #VALUE!. January 31 + 1 month is March 3, as in PHP; DATE.ADD below keeps the end of the month instead.
Adding and counting new recipes
| Function | Gives | Example |
|---|---|---|
DATE.ADD(date, n, unit, [country]) |
The date moved by n units; negative n goes back | DATE.ADD({item's Start}, 3, "months") |
DATE.DIFF(start, end, unit, [country]) |
Whole units from start to end | DATE.DIFF({item's Start}, {item's End}, "days") |
DATE.START(date, period) |
The first day of the week (from Monday), month, quarter or year |
DATE.START(TODAY(), "month") |
DATE.END(date, period) |
The last day of it | DATE.END({item's Date}, "quarter") |
Units: minutes, hours, days, weeks, months, quarters, years, workdays. With months and years, January 31 + 1 month is February 28.
Working days and public holidays new recipes
Working days are Monday to Friday. Add a country code — "IT", "US", "GB", "DE", "FR", "ES" and 200 more — and its public holidays are skipped too.
| Function | Gives | Example |
|---|---|---|
DATE.ADD(date, n, "workdays", [country]) |
n working days later | DATE.ADD({item's Order date}, 3, "workdays", "IT") |
DATE.NEXT_WORKDAY(date, [country]) |
The working day after the date | DATE.NEXT_WORKDAY("2026-12-24", "IT") → 2026-12-28 |
DATE.WORKDAY_FROM(date, [country]) |
The date if it’s a working day, otherwise the next one | DATE.WORKDAY_FROM("2026-11-08", "IT") → 2026-11-09 |
DATE.DIFF(start, end, "workdays", [country]) |
Working days after start up to end | DATE.DIFF("2026-12-23", "2027-01-07", "workdays", "IT") → 8 |
DATE.IS_WORKDAY(date, [country]), DATE.IS_WEEKEND(date), DATE.IS_HOLIDAY(date, country) |
TRUE or FALSE | DATE.IS_HOLIDAY("2026-10-04", "IT") → TRUE |
DATE.HOLIDAY(date, country, [language]) |
The holiday’s name, or empty | DATE.HOLIDAY("2026-12-25", "IT", "en") → Christmas Day |
Public holidays come from the date-holidays project (CC BY-SA 4.0).
Names and formats
| Function | Gives | Example |
|---|---|---|
DATE.FORMAT(date, format, [language]) new recipes |
The date written with monday’s codes, names in the language given | DATE.FORMAT({item's Date}, "dddd D MMMM YYYY", "it") → lunedì 9 novembre 2026 |
DATE.WEEKDAY_NAME(date, [language]) new recipes |
The day’s name | DATE.WEEKDAY_NAME({item's Date}, "it") → lunedì |
DATE.MONTH_NAME(date, [language]) new recipes |
The month’s name | DATE.MONTH_NAME({item's Date}) → November |
DATE.QUARTER(date), DATE.WEEK(date) new recipes |
The quarter (1–4), the ISO week number | DATE.WEEK("2026-01-01") → 1 |
TEXT(date, format) |
The spreadsheet way, Excel codes | TEXT({item's Date}, "DD/MM/YYYY") |
Codes of DATE.FORMAT (the same as monday’s FORMAT_DATE): YYYY 2026, YY 26, MMMM November, MMM Nov, MM 11, M 11, DD 09, D 9, Do 9th, dddd Monday, ddd Mon, HH 14, hh 02, h 2, mm 05, ss 09, A PM. Text in square brackets is written as it is: "D MMM [at] HH:mm" → 9 Nov at 14:05. Without a language, names are in English.
Step by step new recipes
Every DATE function can follow a date with a dot:
={item's Date}.ADD(1, "month").START("month").WORKDAY_FROM("IT").FORMAT("DD/MM/YYYY")This gives the first working day of the next month, as 01/12/2026. A list of dates works too: DATE.FORMAT of a list formats every date.
The spreadsheet date functions
They work in every recipe, old and new, and give date numbers:
| Function | Gives |
|---|---|
TODAY(), NOW() |
Today, now |
DATE(year, month, day), TIME(h, m, s) |
A date, a time |
DATEVALUE(text), TIMEVALUE(text) |
The date or time in a text |
YEAR, MONTH, DAY, HOUR, MINUTE, SECOND |
A part of a date |
WEEKDAY(date, [type]), WEEKNUM(date), ISOWEEKNUM(date) |
Day of the week, week number |
DAYS(end, start), DATEDIF(start, end, unit), YEARFRAC(start, end) |
Distances between dates |
EDATE(date, months), EOMONTH(date, months) |
A date months later, the end of that month |
WORKDAY(date, days, [holidays]), NETWORKDAYS(start, end, [holidays]) |
Working days, with holidays you list yourself |
=TEXT(WORKDAY({item's Date}, 5), "YYYY-MM-DD") → five working days later, for a Date columnmonday’s own date functions (ADD_DAYS, FORMAT_DATE, HOURS_DIFF, WORKDAYS…) work too: see Functions of the monday Formula column.