Working with Dates in Formula Fields

Date calculations in Amazing Fields work differently from what you might expect in a spreadsheet. If you've tried to subtract two date fields and got an unexpected result, or no result at all, this article explains why, and shows you exactly how to do it correctly.

Why date fields work differently

In Excel or Google Sheets, dates are stored as numbers under the hood, which means you can subtract them, add values, and compare them with standard operators like >, <, and =.

In Amazing Fields, date fields are stored as date objects, not numbers. This means:

You cannot subtract one date field from another using -
You cannot use > or < to compare two date fields directly
Standard math operators on dates will either return an error or an incorrect value

Date field functions

Amazing Fields includes five custom functions specifically for date arithmetic. These are not standard spreadsheet functions, they are built into Amazing Fields to handle how date fields work on Trello cards.

Supported units for all date field functions: day, week, month, hour, minute, second

Function	Syntax	Description
DATE_ADD	DATE_ADD('1/1/2012', 3, 'day')	Adds a whole number of time units to a date and returns the new date.
DATE_SUBTRACT	DATE_SUBTRACT('1/1/2012', 3, 'day')	Subtracts a whole number of time units from a date and returns the new date.
DATE_DIFF	DATE_DIFF('1/1/2012', '1/8/2012', 'hour') OR DATE_DIFF('1/1/2012', '1/8/2012', 'hour', true)	Returns the difference between two dates, expressed in the specified unit. Rounds down to whole units by default. Adding true as the last parameter will return the number in floating point instead of rounding to whole units.
DATE_STARTOF	DATE_STARTOF('1/1/2012', 'month')	Returns the start of the time period (e.g. the start of the month) that contains the given date.
DATE_ENDOF	DATE_ENDOF('1/1/2012', 'month')	Returns the end of the time period (e.g. the end of the week) that contains the given date.

DATE_ADD: Add time to a date

Use DATE_ADD when you want to calculate a future date by adding a set amount of time to an existing date field.

Syntax: DATE_ADD(date, number, unit)

Examples

Scenario	Formula	Result
Add 3 days to a date	`DATE_ADD({Due Date}, 3, 'day')`	3 days after the due date
Add 2 weeks to a start date	`DATE_ADD({Start Date}, 2, 'week')`	2 weeks after start
Add 1 month to today	`DATE_ADD(TODAY(), 1, 'month')`	Same day next month
Set a 30-day deadline from a field	`DATE_ADD({Created Date}, 30, 'day')`	30 days after creation

Tip: You can use TODAY() as the date input if you want to calculate relative to the current date rather than a date field.

DATE_SUBTRACT: Go back in time from a date

Use DATE_SUBTRACT when you want to calculate a past date, or set a date a certain amount of time before another.

Syntax: DATE_SUBTRACT(date, number, unit)

Examples

Scenario	Formula	Result
7 days before a deadline	DATE_SUBTRACT({Due Date}, 7, 'day')	One week before due
Reminder 2 weeks before event	DATE_SUBTRACT({Event Date}, 2, 'week')	2 weeks before event
Start of previous month	DATE_SUBTRACT(DATE_STARTOF({Date Field}, 'month'), 1, 'month')	Previous month start

DATE_DIFF: Calculate the gap between two dates

Use DATE_DIFF when you want to know how much time has passed, or how much time remains, between two dates.

Syntax:

DATE_DIFF(date1, date2, unit)
DATE_DIFF(date1, date2, unit, true) // returns decimal instead of whole number

By default, DATE_DIFF rounds down to the nearest whole unit. For example, a gap of 6.8 days would return 6. Add true as a fourth argument to get the precise decimal value instead.

Examples

Scenario	Formula	Result
Days between start and due date	DATE_DIFF({Start Date}, {Due Date}, 'day')	Number of days (whole)
Weeks remaining until deadline	DATE_DIFF(TODAY(), {Due Date}, 'week')	Weeks until due
Hours between two timestamps	DATE_DIFF({Start}, {End}, 'hour')	Whole hours elapsed
Precise days (with decimals)	DATE_DIFF({Start Date}, {Due Date}, 'day', true)	Decimal day count
Days overdue (negative = overdue)	DATE_DIFF({Due Date}, TODAY(), 'day')	Negative if past due

Tip: To show whether a card is overdue, combine DATE_DIFF with an IF function: IF(DATE_DIFF({Due Date}, TODAY(), 'day') > 0, 'Overdue', 'On Track')

DATE_STARTOF and DATE_ENDOF: Snap to period boundaries

These two functions return the beginning or end of a time period that contains a given date. They are useful for grouping, reporting, or setting deadlines that align to calendar boundaries.

Syntax:

DATE_STARTOF(date, unit)
DATE_ENDOF(date, unit)

Examples

Scenario	Formula	Result
Start of the current month	DATE_STARTOF(TODAY(), 'month')	First day of this month
End of the current week	DATE_ENDOF(TODAY(), 'week')	Last moment of this week
Start of the year for a date field	DATE_STARTOF({Due Date}, 'year')	Jan 1 of that year
End of day for a date field	DATE_ENDOF({Date Field}, 'day')	11:59 PM on that day

Combining Date Functions

Date field functions can be nested and combined with other Amazing Fields formula functions to build more powerful calculations.

Is a card overdue?

IF(DATE_DIFF({Due Date}, TODAY(), 'day') > 0, 'Overdue', 'On Track')

Days remaining until due (show 0 if already passed)

MAX(0, DATE_DIFF(TODAY(), {Due Date}, 'day'))

Calculate a review date 90 days after creation

DATE_ADD({Created Date}, 90, 'day')

Count working days between two dates

NETWORKDAYS({Start Date}, {Due Date})

Tip: NETWORKDAYS is a standard spreadsheet function that also works in Amazing Fields, you don't need a date field function for this one.

Using TODAY() and NOW() in Formulas

You can reference the current date and time in any formula using these two standard functions:

• TODAY() — Returns today's date (time is midnight)

• NOW() — Returns the current date and time, including hours and minutes

These work as the date input for any of the field functions above. For example:

Formula	Description
DATE_DIFF(TODAY(), {Due Date}, 'day')	days until due from today
DATE_ADD(NOW(), 48, 'hour')	48 hours from right now

Note: Formulas using TODAY() or NOW() will recalculate each time the card is opened or updated. This is expected behavior.