Forum Discussion
Table showing Weekdays and Alternating Saturdays with dates beginning 4/11/2022
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.
| Monday | 4/11/2022 |
| Tuesday | 4/12/2022 |
| Wednesday | 4/13/2022 |
| Thursday | 4/14/2022 |
| Friday | 4/15/2022 |
| Monday | 4/18/2022 |
| Tuesday | 4/19/2022 |
| Wednesday | 4/20/2022 |
| Thursday | 4/21/2022 |
| Friday | 4/22/2022 |
| Saturday | 4/23/2022 |
| Monday | 4/25/2022 |
| Tuesday | 4/26/2022 |
| Wednesday | 4/27/2022 |
| Thursday | 4/28/2022 |
| Friday | 4/29/2022 |
| Monday | 5/2/2022 |
| Tuesday | 5/3/2022 |
| Wednesday | 5/4/2022 |
| Thursday | 5/5/2022 |
| Friday | 5/6/2022 |
| Saturday | 5/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.
As variant
with
=WORKDAY.INTL( C2, 1, 1 + ISEVEN( INT( (WORKDAY.INTL( C2, 1, 1 )-$C$2)/7 ) )*10)
5 Replies
As variant
with
=WORKDAY.INTL( C2, 1, 1 + ISEVEN( INT( (WORKDAY.INTL( C2, 1, 1 )-$C$2)/7 ) )*10)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.
You can use a helper column ( A in this case)
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
- Thank you for your response.
