TIP - Find the date of the first Sunday of the month
This little formula is useful for a range of solutions, like working out if a date is in daylight savings time or not. The examples below reference a date field named [Date].
The first part of the formula finds the date of the seventh day of the month. This is done so that the number of days from Sunday can be subtracted:
= DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 )
The second part of the formula finds the number of days to subtract from that date:
- ( WEEKDAY( DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) ) - 1 )
This part of the formula gets the date of the seventh day of the month again, and works out what day of the week it is. We subtract 1 from the result because the WEEKDAY() function returns values starting at Sunday = 1. So if the seventh day of the week just happens to be Sunday we don't want to subtract 1, we need to subtract 0 instead.
So the entire formula to find the date of the first Sunday of a month is:
= DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) - ( WEEKDAY( DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) ) - 1 )
This can be tweaked a little for applications like checking if a date is/was in daylight savings time. This example expects daylight savings time to start on the first Sunday in October and end on the first Sunday in April of the next year:
=IF( AND( [Date]@row >= DATE( YEAR( [Date]@row ), 10, 7 ) - ( WEEKDAY( DATE( YEAR( [Date]@row ), 10, 7 ) ) - 1 ), [Date]@row < DATE( YEAR( [Date]@row ) + 1, 4, 7 ) - ( WEEKDAY( DATE( YEAR( [Date]@row) + 1, 4, 7) ) - 1) ), 1, 0)
It checks to see if [Date] is greater than or equal to the first Sunday in October (month 10) in the same year as [Date] AND is less than the fist Sunday of April (month 4) in the following year. If that is true then it returns 1 (daylight savings), otherwise 0 (not daylight savings).
To find the date of the first day of the month for some other weekday a slight tweak is required on the second part of the formula:
= DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) - MOD( ( WEEKDAY( DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) ) - 1 ) + [Weekday], 7)
where [Weekday] contains:
- 0 for Sunday
- 1 for Monday
- 2 for Tuesday
- 3 for Wednesday
- 4 for Thursday
- 5 for Friday
- 6 for Saturday
