Shape · Dates and time

Date/Time Arithmetic

Adds or subtracts time from a date, or measures the gap between two dates, from seconds to years or working days. For example, due date = order date + 30 days.

Also known as: Date diff, date add, business days

How it works

Click Add operation and choose Add interval or Difference. Add interval takes a date column, an amount as a Number or from a Column, and a unit from microsecond to year, including working days; a negative amount subtracts. A blank output name changes the column in place. Whole-day units keep a date a date, while hours and smaller turn it into a timestamp, and adding a month lands on the last valid day. Difference takes a from and a to, each a column, a typed date or today, and a unit; the result is to minus from, negative when to is earlier, and the default name is <from>_to_<to>_<unit>s. A null on either side gives null. working days counts Monday to Friday, both endpoints included for a difference; wiring a table of dates into the holidays port that appears and filling in Holiday calendar skips those dates too, per region if you name a region column on both tables.

Inputs and outputs

Settings

operations: [{kind:"add_interval", column, amount|amountColumn(+amountSource:"column"), unit, outputColumn?} | {kind:"diff", fromColumn|fromValue(+fromSource:"value"|"now"), toColumn|toValue(+toSource), unit, outputColumn?}]; unit "working_day" = business days (Mon–Fri). A "working_day" op grows an optional `holidays` input port: wire a table of dates and set holidayDateColumn to skip them too; add holidayRegionColumn + regionColumn (the matching column on this table) for per-row multi-region calendars

Example

Invoices are due 30 days after the order and you want to see how late each one is. Add an Add interval on order_date, Number 30, unit day, output due_date, then a Difference from due_date to today in day, output days_late.

More in Dates and time

• Extract Date/Time Parts — Pulls parts out of a date into new columns: year, quarter, month, week, day, weekday, hour, a weekend flag and more.
• Truncate Date/Time — Rounds dates down to the start of a period, such as the first of the month. Handy before grouping by month.
• Time of Day — Drops the date from a timestamp and keeps the clock time, so you can compare times across days.
• Make Date from Parts — Combines separate year, month and day columns (and hour, minute, second) into one real date or timestamp.
• Convert Date/Time to Text — Turns a date into text using a pattern, such as 01/03/2024 or Mar 2024. The result is text, so use it for labels and exports, not for date math.
• Convert Serial Date — Converts between numbers and dates: Unix timestamps, Excel serial dates, Julian days or your own starting point. For example, 45292 from Excel becomes 2024-01-01.
• Convert Timezone — Shifts times from one time zone to another, with daylight saving handled. For example, UTC to Europe/Warsaw.