The dateSubtract()
function accepts a date argument and subtracts from it, returning a new date.
dateSubtract(date, number, string [from unit list])
It requires three arguments in the following order:
Accepted units include:
- “years”
- “quarters”
- “months”
- “weeks”
- “days”
- “hours”
- “minutes”
- “seconds”
- “milliseconds”
Example Formulas
// Assume a property called "Date" with a current row value
// of June 1, 2022
dateSubtract(prop("Date"),3,"months") // Output: March 1, 2022
dateSubtract(prop("Date"),5,"days") // Output: May 27, 2022
You can nest multiple dateSubtract()
functions to subtract multiple different types of units from a date:
// Assume a property called "Date" with a current row value
// of June 1, 2022
dateSubtract(dateSubtract(prop("Date"),3,"months"),5,"days")
// Output: February 24, 2022
dateSubtract()
accepts negative values:
// Assume a property called "Date" with a current row value
// of June 1, 2022
dateSubtract(prop("Date"), -3, "months") // Output: September 1, 2022
Example Database
This example database demonstrates how to output a date object set to January 1, 12:00 AM in the current year, in your current time zone.

View and Duplicate Database

“Jan 1” Property Formula
// Compressed
dateSubtract(dateSubtract(dateSubtract(dateSubtract(now(), minute(now()), "minutes"), hour(now()), "hours"), date(now()) - 1, "days"), month(now()), "months")
// Expanded
dateSubtract(
dateSubtract(
dateSubtract(
dateSubtract(
now(),
minute(
now()
),
"minutes"
),
hour(
now()
),
"hours"
),
date(
now()
) - 1,
"days"
),
month(
now()
),
"months"
)
This formula uses multiple instances of dateSubtract()
to remove different units of time from the output of the now function in order to arrive at Jan 1, 12:00 AM.
The amount of time that is subtracted from now()
is actually derived from now()
itself. For example:
dateSubtract(
now(),
minute(
now()
),
"minutes"
)
Here, minute(now())
gets the minute value from now()
.
So, if it’s currently 3:12 PM, this essentially says:
Subtract 12 minutes from 3:12 PM.
The resulting output would be 3:00 PM.
The formula then takes this output and does the same thing using hour, date, and month.
The only caveat is that 1
must be substracted from the output of date()
, since there is no “January 0”.
Other formula components used in this example:





