FirstDayOfPeriod(Date date, Duration period, Date reference)

Returns the first day of the period in which the given date falls.

Parameters

date

The date for which to calculate the first day of the period.

period

The duration representing the cycle of periods.

reference

The starting date of the sequence.

Returns

Date

Remarks

The cycle of periods is defined by the given period duration, repeated in sequence starting from the provided reference date. If the period is not a whole number of days, the fractional part is ignored.

Examples

To find the first day of this week, assuming the week starts on Sunday (using 1/1/2006 as the reference for Sunday), you can use:
FirstDayOfPeriod(Today(), Days(7), Date(2006,1,1))

For weeks starting on Monday, you can adjust the reference date, for example:
FirstDayOfPeriod(Today(), Days(7), Date(2007,1,1))

When setting up views that include specific date ranges, you can select "This Week", "Last Week", and "Next Week."

For columns named Date and checking for this/last/next week, you can use these formulas:

This Week:
FirstDayOfPeriod([Date], Days(7), Date(2006,1,1)) = FirstDayOfPeriod(Today(), Days(7), Date(2006,1,1))

Last Week:
FirstDayOfPeriod([Date], Days(7), Date(2006,1,1)) = FirstDayOfPeriod(Today(), Days(7), Date(2006,1,1)) - Days(7)

Next Week:
FirstDayOfPeriod([Date], Days(7), Date(2006,1,1)) = FirstDayOfPeriod(Today(), Days(7), Date(2006,1,1)) + Days(7)