Dates and times

Dates are the most common source of errors. Three functions solve almost every case.

The format monday.com uses

A Date column returns text in YYYY-MM-DD format, like 2013-02-08, or YYYY-MM-DD HH:MM:SS when the column includes the time. To write a date, the formula must produce text in the same format. Times are in UTC.

DATEVALUE, math, TEXT

To calculate with a date, follow three steps:

  1. DATEVALUE turns the text into a real date: DATEVALUE({item's Date});
  2. do the math: + 5 adds five days, WORKDAY(..., 5) adds five working days;
  3. TEXT turns the date back into the format monday.com accepts: TEXT(..., "YYYY-MM-DD").
TEXT(DATEVALUE({item's Date}) + 5, "YYYY-MM-DD")
Forgot TEXT?

Without TEXT the result is a serial number like 45293, and the Date column rejects it or shows a date in a strange year. If the target shows a weird date, check that the formula ends with TEXT(..., "YYYY-MM-DD").

Copying dates without math

When you only move dates around, no conversion is needed. For example, a Timeline from two Date columns:

CONCATENATE({item's From}, "+", {item's To})

Timelines

A Timeline is start+end. Take the start with LEFT({item's Timeline}, 10) and the end with RIGHT({item's Timeline}, 10).

NETWORKDAYS(DATEVALUE(LEFT({item's Timeline}, 10)), DATEVALUE(RIGHT({item's Timeline}, 10)))

Today and relative dates

  • Today: TEXT(TODAY(), "YYYY-MM-DD")
  • Days until a deadline: DATEVALUE({item's Due date}) - TODAY()
  • End of the month: TEXT(EOMONTH(TODAY(), 0), "YYYY-MM-DD")
  • Day of the week (1 = Monday): WEEKDAY(DATEVALUE({item's Date}), 2)
TODAY() is evaluated when the recipe runs

A formula with TODAY() is calculated when its trigger fires, not every day. A "days left" value stays the same until the item changes again.

Times and durations

  • Hour column: text like 17:30; convert it with TIMEVALUE to calculate.
  • Time tracking: text like 01:23:45; use HOUR, MINUTE and SECOND to split it.
  • Date with time: TEXT(DATEVALUE({item's Date}) + TIMEVALUE({item's Date}) + 2/24, "YYYY-MM-DD HH:MM:SS") adds two hours.

More examples in the Ideas: working days, working hours, next birthday, first Monday of the month.

Something unclear or missing? Tell us and we'll improve this page.