Apr 10 2022 05:12 PM
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.
Apr 10 2022 08:10 PM
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
Apr 11 2022 03:49 AM
SolutionAs variant
with
=WORKDAY.INTL( C2, 1, 1 + ISEVEN( INT( (WORKDAY.INTL( C2, 1, 1 )-$C$2)/7 ) )*10)
Apr 11 2022 11:37 AM
While I do not understand the formula, the table works and I thank you so much for your help.
Apr 11 2022 11:50 AM
Apr 11 2022 01:12 PM
@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.
Apr 11 2022 03:49 AM
SolutionAs variant
with
=WORKDAY.INTL( C2, 1, 1 + ISEVEN( INT( (WORKDAY.INTL( C2, 1, 1 )-$C$2)/7 ) )*10)