Date & time operators

Reference for all date and time operators supported in Noloco formula fields, including TODATE, DATE, DAY, DAYS, WORKDAY, EDATE, EOMONTH, HOUR, MINUTE, SECOND, MONTH, YEAR, WEEKNUM, ISOWEEKNUM, YEARFR

All date and time operators supported in Noloco formulas. To return a value typed as a Date, wrap the result in TODATE.

Available operators: +, -, TODATE, DATE, DATEVALUE, DAY, DAYS, WORKDAY, EDATE, EOMONTH, HOUR, ISOWEEKNUM, MINUTE, MONTH, NOW, SECOND, TIME, TIMEVALUE, TODAY, WEEKNUM, YEAR, YEARFRAC.


+

date + numberOfDays

Adds a number of days to a date.

Example: date + 1

-

date - numberOfDays

Subtracts a number of days from a date.

Example: date - 2

TODATE

TODATE(date)

Converts a date to the correct output format for a Noloco date. Required wrapper when a formula's output type should be Date.

Example: TODATE(important date + 7)

DATE

DATE(year, month, day)

Converts a provided year, month, and day to a serial date.

Example: DATE(2024, 6, 3)

DATEVALUE

DATEVALUE(dateText)

Converts a date from text in one of the two formats "mm-dd-yyyy" or "yyyy-mm-dd" to a serial date.

Example: DATEVALUE("2024-06-03")

DAY

DAY(date)

Returns the day component of a date.

DAYS

DAYS(end, start)

Returns the number of days between two dates.

WORKDAY

WORKDAY(start, numberOfDays, [holidays])

Date representing the number of working days before or after the starting date. Working days exclude weekends and any dates identified as holidays.

Example: WORKDAY(start_date, 3, {"2024-06-03","2024-12-25"})

EDATE

EDATE(date, numberOfMonths)

Date representing a date a given number of months before or after a provided date.

EOMONTH

EOMONTH(date, numberOfMonths)

Date representing the last day of a month a given number of months before or after a provided date.

HOUR

HOUR(datetime)

The hours from a datetime or time value.

ISOWEEKNUM

ISOWEEKNUM(date)

The ISO week number a date falls on.

MINUTE

MINUTE(datetime)

The minutes from a valid datetime or time value.

MONTH

MONTH(date)

The month of a provided date in numeric format.

NOW

NOW()

Returns the current date and time as a serial number. Useful for date arithmetic against the current moment.

Example: NOW() - start_date

SECOND

SECOND(datetime)

The seconds from a valid datetime or time value.

TIME

TIME(hour, minute, second)

Converts a provided hour, minute, and second to a fractional time.

TIMEVALUE

TIMEVALUE(timeText)

Converts a time in an accepted format to a valid fractional time. Accepts: "1:10 AM", "1:10:05 AM", "18:20", "18:20:15".

TODAY

TODAY()

Returns the current date as a serial number. Useful for date arithmetic like "days until due".

Example: due_date - TODAY()

WEEKNUM

WEEKNUM(date)

The week number the date falls on.

YEAR

YEAR(date)

The year from a date.

YEARFRAC

YEARFRAC(start, end)

The number of years (and fractional years) between two dates.


Not supported

The following spreadsheet-style date functions are not supported in Noloco formulas. Use the alternatives listed below.

Not supported
Use instead

NETWORKDAYS

WORKDAY β€” returns a date after N working days. For counting working days between two dates, there is no direct equivalent; combine DAYS with custom logic.

DATEADD

The + operator β€” e.g. date + 7 adds 7 days. To add months, use EDATE.


Last updated

Was this helpful?