SOLVED

Table showing Weekdays and Alternating Saturdays with dates beginning 4/11/2022

New Contributor

I would like to create a table in Excel Windows 10 that using a formula to automatically show weekday names with dates (month/day/year) and includes every other Saturday beginning with the date of 4/11/2022 and ending 3/31/2023.

 

Monday4/11/2022
Tuesday4/12/2022
Wednesday4/13/2022
Thursday4/14/2022
Friday4/15/2022
Monday4/18/2022
Tuesday4/19/2022
Wednesday4/20/2022
Thursday4/21/2022
Friday4/22/2022
Saturday4/23/2022
Monday4/25/2022
Tuesday4/26/2022
Wednesday4/27/2022
Thursday4/28/2022
Friday4/29/2022
Monday5/2/2022
Tuesday5/3/2022
Wednesday5/4/2022
Thursday5/5/2022
Friday5/6/2022
Saturday5/7/2022

and so on. 

 

The above sample was done manually. I would like the table to autopopulate the information.

Thank you in advance for any assistance.

 

 

 

 

 

5 Replies

@Dianna Siderio 

 

You can use a helper column ( A in this case)

 

AlbertG_1-1649646083747.png

Use the formula '=IF(MOD((D4-D$3),14)=12,2,1)' in each cell of column A - this compares the date for that row's date with the start date of Sunday 10/04 ( or 04/10 in US format).

 

If its  the 12th day of the fortnight ie every 2nd Friday then the next row will add 2 days instead of 1 ie skips the Saturday

 

best response confirmed by Hans Vogelaar (MVP)
Solution

@Dianna Siderio 

As variant

image.png

with

=WORKDAY.INTL( C2, 1, 1 + ISEVEN( INT( (WORKDAY.INTL( C2, 1, 1 )-$C$2)/7 ) )*10)

@Sergei Baklan 

 

While I do not understand the formula, the table works and I thank you so much for your help.

@Dianna Siderio , you are welcome.

 

With WORDAY.INTL we add to each previous date (first parameter) one day (second parameter) skipping weekend (third parameter). If third parameter = 1 we skip Sat and Sun, if = 11 when Sun only.

We make third parameter calculatable using 1 by default. If number of days between added one and very first date, divided by 7, is even (or, other words, even week number from start date), we add 10 to third parameter. Thus it is 11 for this week.