How to Generate a list of 2nd and 4th Saturday in each month of Year

Today we are going to discuss about different methods in excel to generate a list of 2nd and 4th Saturday in each month of the year. Lot to learn, let's dive in.

Input cell for year is C1,

Based on the given year 2nd and 4th Saturday is populated in the range D4:D27.

Method - 1:

Formula in D4 cell is,

=IF(ROWS($D$4:D4)>=25,"",FLOOR(DATE($C$1,INT((ROWS($D$4:D4)-1)/2)+1,2*(MOD(ROWS($D$4:D4)-1,2)+1)*7),7))

Don't panic, i will guide you through each step,

Create Month series:

Our requirement is to create 2 dates in each month, So, we have to find a way to create a sequence of 1;1;2;2;3;3. Then we can feed this in month argument of DATE function.

=INT((ROWS($D$4:D4)-1)/2)+1

In 1st row,

Rows function delivers 1,

then 1 - 1 is 0, divide this by 2 we get 0. Integer returns 0 and we add 1 to get 1 as output.

In 2nd row,

Rows function delivers 2,

then 2 - 1 is 1, divide this by 2 we get 0.5. Integer returns 0 and we add 1 to get 1 as output.

In 3rd row,

Rows function delivers 3,

then 3 - 1 is 2, divide this by 2 we get 1. Integer returns 1 and we add 1 to get 2 as output.

In 4th row,

Rows function delivers 4,

then 4 - 1 is 3, divide this by 2 we get 1.5. Integer returns 1 and we add 1 to get 2 as output.

Create Date series:

=2*(MOD(ROWS($D$4:D4)-1,2)+1)*7

In 1st row,

Rows function delivers 1,

then 1 - 1 is 0, MOD function helps us to get the reminder after division by given divisor.

Here, divisor is 2. So, reminder is 0 and we add 1 to get 1 as result.

2*1*7 = 14

In 2nd row,

Rows function delivers 2,

then 2 - 1 is 1, divisor is 2. So, reminder is 1 and we add 1 to get 2 as result.

2*2*7 = 28

In 3rd row,

Rows function delivers 3,

then 3 - 1 is 2, divisor is 2. So, reminder is 0 and we add 1 to get 1 as result.

2*1*7 = 14

Now, feed this month and days in DATE function.

=DATE($C$1,INT((ROWS($D$4:D4)-1)/2)+1,2*(MOD(ROWS($D$4:D4)-1,2)+1)*7)

If we drag this formula down, we get,

={45305;45319;45336;45350;45365;45379;45396;45410;...}

Date version of above numbers are,

={"14/01/2024";"28/01/2024";"14/02/2024";"28/02/2024";"14/03/2024";"28/03/2024";...}

To round this down to nearest multiple of 7, We wrap above DATE construction inside FLOOR function,

=FLOOR(DATE($C$1,INT((ROWS($D$4:D4)-1)/2)+1,2*(MOD(ROWS($D$4:D4)-1,2)+1)*7),7)

Date version of above construction,

={"13/01/2024";"27/01/2024";"10/02/2024";"24/02/2024";"09/03/2024";"23/03/2024";...}

All dates belong to Saturdays. This is dynamic, change the year in C1 cell to some other year and check the result.

Method - 2:

=FLOOR(DATE($C$1,INT((SEQUENCE(24)-1)/2)+1,2*(MOD(SEQUENCE(24)-1,2)+1)*7),7)

This is single cell formula and can only be accessed in the excel version 2019 or above.

This will spill the results for 24 cells, no need to drag it down.

Here, we hardcoded 24 in sequence function, this is because, we expect 2 dates in each month of a year. So maximum of 24 entries possible in our result.

All other concepts are same, only ROWS function is replaced by SEQUENCE function.