=DATEPATTERN

The =DATEPATTERN function generates a list of future dates based on a flexible, human-readable recurrence pattern.

It is designed to express real-world schedules such as work rotations, availability calendars, and recurring events.

This function supports:

Weekly, bi-weekly, and monthly patterns
Specific weekdays
First/third week or nth-weekday-of-month rules
Combined rules (OR logic)
Explicit date exclusions
Anchored repeating cycles

Syntax

=DATEPATTERN(DateRecurrencePattern; [LatestDateToGenerate]; [RecurrenceAnchorDate]; [StartFromStartDate])

Parameters

DateRecurrencePattern

Required: Yes

A text pattern describing the recurrence rules.

LatestDateToGenerate

Required: No

The latest date to generate results for (inclusive).

Generation stops once this date is reached
Default: 7 days from today

RecurrenceAnchorDate

Required: No

The anchor date used to calculate repeating cycles (for example, every other week).

Determines alignment for alternating and monthly patterns
Default: today

StartFromStartDate

Required: No

The date after which generation begins.

Dates on or before this value are never returned
Default: today (generation begins tomorrow)

Parameter Behavior

Only dates after StartFromStartDate are returned (the start date itself is not included)
Date generation stops at LatestDateToGenerate
Time-of-day values are ignored
Weeks are always Monday-based

How DATEPATTERN Works

The recurrence pattern is parsed into one or more rules
Repeating cycles are calculated starting from RecurrenceAnchorDate
The function scans forward one day at a time beginning the day after StartFromStartDate
A date is selected if it matches any rule
Excluded dates are removed
The process continues until LatestDateToGenerate is reached

Pattern Overview

A recurrence pattern consists of one or more rules, optionally followed by exclusions.

rule [+ rule ...] [x{exclusions}]

+ means OR (a date only needs to match one rule)
x{} removes specific dates even if they otherwise match

Frequency (f)

Every rule begins with a frequency declaration:

f<interval><unit>

Units

d = Daily
w = Weekly
m = Monthly

Examples

f1d = Every day
f1w = Every week
f2w = Every other week
f1m = Every month

Weekly and monthly patterns are anchored to RecurrenceAnchorDate.

Day-of-Week Filter (d{})

Use d{} to restrict a rule to specific days of the week.

Days use Monday = 1 … Sunday = 7.

Value	Day
1	Monday
2	Tuesday
3	Wednesday
4	Thursday
5	Friday
6	Saturday
7	Sunday

Examples

d{12345} = Weekdays (Monday–Friday)
d{6} = Saturday
d{135} = Monday, Wednesday, Friday

Week-of-Month Filter (w{})

Use w{} to match specific weeks of the month.

Weeks are Monday-based
Week 1 starts on the Monday of the week containing the 1st of the month

Examples

w{1} = First week of the month
w{13} = First and third weeks
f1m w{13} = First and third week of every month

Nth-Weekday-of-Month Filter (n{})

Use n{} to match the nth occurrence of a weekday within a month.

This filter is typically combined with d{}.

Examples

d{6} n{2} = Second Saturday of the month
d{3} n{13} = First and third Wednesday
f1m d{6} n{23} = Second and third Saturday every month

Combining Rules (+)

Use + to combine multiple rules.

A date is included if any rule matches.

Examples

Every weekday plus every other Saturday

f1w d{12345} + f2w d{6}

Monday, Tuesday, Thursday, Friday plus every other Wednesday

f1w d{1245} + f2w d{3}

Every week plus the 2nd and 3rd Saturday of the month

f1w d{12345} + f1m d{6} n{23}

Excluding Dates (x{})

Exclusions remove dates after all rules are evaluated.

Single Date Exclusion

x{2025-12-25}

Date Range Exclusion (Inclusive)

x{2025-12-24..2025-12-31}

Multiple Exclusions

x{2025-12-25,2026-01-01..2026-01-03}

Example with Exclusions

f1w d{12345} + f2w d{6} x{2025-12-25}

Anchor Date Explained

The RecurrenceAnchorDate determines how repeating cycles are aligned.

For f2w (every other week), the week containing the anchor date is treated as the first active week
For monthly patterns, the anchor month is treated as the first cycle

Changing the anchor date changes how alternating schedules align.

Important Notes

Weeks always start on Monday
Exclusions always override matching rules
Rules are evaluated using OR logic
Dates are generated after StartFromStartDate through LatestDateToGenerate
All dates are calendar dates (time-of-day is ignored)

Example Usage

=DATEPATTERN(
  f1w d{12345} + f2w d{6} x{2025-12-25};
  2025-12-31;
  2025-12-01;
  2025-12-18
)

Result:

All dates after December 18, 2025 through December 31, 2025 that match weekdays plus every other Saturday, excluding December 25th.

2025-12-19;
2025-12-20;
2025-12-22;
2025-12-23;
2025-12-24;
2025-12-26;
2025-12-29;
2025-12-30;
2025-12-31

When to Use DATEPATTERN

Use DATEPATTERN when you need:

Predictable personnel schedules
Alternating or rotating work patterns
Compact, readable recurrence definitions
Automatically generated future dates within a known date range

If a schedule can be described in words, it can almost always be expressed using DATEPATTERN.

Note: Formula functions are case-sensitive and must be written in ALL CAPS.

Keywords

DATEPATTERN, recurrence pattern, recurring dates, future dates, date generation, schedule automation, work rotations, availability calendar, repeating events, weekly schedule, bi-weekly schedule, monthly schedule, specific weekdays, nth-weekday-of-month, first/third week, combined rules, exclusions, anchored cycles, calendar scheduling, date rules, workflow formulas, process field dates, time-series planning