dateSubtract

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:

1. A date (must be an actual date data type)
2. A number
3. A unit

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

Notion – The all-in-one workspace for your notes, tasks, wikis, and databases.

A new tool that blends your everyday work apps into one. It’s the all-in-one workspace for you and your team

thomasfrank.notion.site

“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:

now – Thomas Frank

thomasjfrank.com

minute – Thomas Frank

thomasjfrank.com

hour – Thomas Frank

thomasjfrank.com

Create a Formula Property – Thomas Frank

thomasjfrank.com

month – Thomas Frank

thomasjfrank.com

subtract – Thomas Frank

thomasjfrank.com