The dateSubtract() function accepts a date argument and subtracts from it, returning a new date.
dateSubtract(date, number, string [from unit list])
date.dateSubtract(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 */
prop("Date").dateSubtract(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 */
prop("Date").dateSubtract(3, "months").dateSubtract(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
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:
now().dateSubtract(
now().minute()
"minutes"
)
Here, now().minute() 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:
