Work with dates and financial years

Before you start

Only Date columns appear in the date dropdowns. If your dates are stored as text, convert them first with Convert Column Type in Column Management. See Convert text to dates and numbers.

Add or subtract days, months or years from a date

Add / Subtract Date Values moves a date forward or back by the amount you set. Use it for deadlines, expiry dates or lining up dates across periods.

1. In Select column, choose the date column.
2. In Select behaviour, choose Add or Subtract.
3. Enter an amount in one or more of the duration fields: Years, Months, Weeks, Days, Hours, Minutes or Seconds. Fields left at 0 are ignored.
4. Optional: click Add Condition to shift only the rows that meet a condition.
5. In Place in, choose New Column and type a name, or choose Existing Column and pick the column to overwrite.
6. Optional: click the calendar icon next to the result column options to pick the date format. The formats come in Universal, US and UK groups.
7. Click Apply.

Example: to set a review date 30 days after a contract starts, select contract_start, choose Add, enter 30 in Days and name the new column review_due.

Get the year, month, weekday or time from a date

Extract Date Part pulls one part out of a date, such as the year, month name, week number or hour, and writes it to a new column.

1. In Select column, choose the date column.
2. In Select date part, choose the part you want. Each part shows an icon for its result type (number, text or date).
3. Name the result column and click Apply.

The pipeline card names the result type in brackets, for example "Month name (text)".

Financial years are not in this list. Use Financial Year, below.

Example: to group sales by month, extract Month name from order_date. The new column holds values such as "January" and "February", ready to use as a group in Group, pivot and unpivot.

Add a financial year column

Financial Year adds a Number column with the financial year each date falls in, such as 2025. A financial year is named by the year it ends in. With a year that starts in October, October 2024 to September 2025 is 2025.

1. In the Transform menu, select Date, Numeric & Text > Financial Year. The Extract Date Part panel opens with a Year starts in dropdown in place of Select date part. It is set to October.
2. In Select column, choose the date column.
3. In Year starts in, choose the month your financial year starts, from January to December.
4. Check the hint under the form. It shows the last day of the old year and the first day of the new one, for example "Sep 30, 2025 becomes 2025, Oct 1, 2025 becomes 2026. The year is named by the year it ends in." The date format follows your browser.
5. Name the result column (the default is "Financial year") and click Apply.

How the financial year number is worked out

The result is a whole number with no decimals and no thousands separator, such as 2026.

• A date in or after the start month belongs to the next year's number.
• A date before the start month keeps its calendar year.
• Choosing January gives the calendar year, the same as extracting Year.

The pipeline card describes the step, for example "Financial year, starts in April (number)". To change the start month later, edit the step and pick another month in Year starts in. The value comes from each row's date, so it only changes between runs if the date does.

Calculate the number of days between two dates

Calculate Date Difference measures the time between two dates and returns a number. Each end can be a date column or the current time in UTC.

1. In Start Date, select Date Column and choose a column, or select Current Time (UTC). With Current Time (UTC), the column dropdown goes away.
2. Set End Date the same way.
3. In Select Unit, choose Years, Months, Weeks, Days, Hours, Minutes or Seconds.
4. In Place in, choose New Column and type a name, or choose Existing Column to overwrite one.
5. Click Apply.

Examples:

• To measure shipping time, set Start Date to order_date, End Date to ship_date, the unit to Days and the name to days_to_ship.
• To count the days since each customer signed up, set Start Date to signup_date and End Date to Current Time (UTC).

How each unit is counted

The result is End Date minus Start Date. It is negative when the end comes before the start.

Years and Months only look at the calendar numbers, so 2024-01-31 to 2024-02-01 counts as 1 month. Use Days when you need an exact span.

Current Time (UTC) is the moment the step runs. A result that uses it changes on every run.

Troubleshooting

Related

• Calculate with formulas: numeric calculations, window functions and ranking.
• Work with text and JSON: extract text, change case and expand JSON.
• Group, pivot and unpivot: group dates by year, month or weekday as you summarise.
• Column types, IDs, currency and percentages: how Mammoth sets a column's type.
• How the pipeline works: where tasks run and how to preview them.