Automatically generate dates for specific days of the week on any given month

T

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
137
Office Version
  1. 365
Platform
  1. Windows
Hello all

I am trying to produce a register for a boxing club which practices on Mondays, Wednesdays, Fridays and Sundays every week. I would like to have the people's names down the left-hand side and the dates going horizontally across the top rows 2 and 3. I would like to be able to change the month and the year (E1 and E2) so I can easily produce a new sheet for every month without having to manually calculate / or fill out the relevant dates. Is there a way of me entering the month and year in E1 and E2 and then rows 2 and 3 automatically populate with the day (E1) and the date (E2) but so that it only auto fills for Mondays, Wednesdays, Fridays and Sundays for every week in the month? I have done the below manually to illustrate what I want.

Many thanks in advance to anyone who is able to help me.

Book3
ABCDEFGHIJK
1Month10
2Year2024
3WednesdayFridaySundayMondayWednesdayFridaySundayMondayWednesday
4First NameSurname02/10/202404/10/202406/10/202407/10/202409/10/202411/10/202413/10/202414/10/202416/10/2024
5JoeBloggs
6SandySmith
7BartSimpson
8MickeyMouse
Sheet1
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Tashat said:
Hello all

I am trying to produce a register for a boxing club which practices on Mondays, Wednesdays, Fridays and Sundays every week. I would like to have the people's names down the left-hand side and the dates going horizontally across the top rows 2 and 3. I would like to be able to change the month and the year (E1 and E2) so I can easily produce a new sheet for every month without having to manually calculate / or fill out the relevant dates. Is there a way of me entering the month and year in E1 and E2 and then rows 2 and 3 automatically populate with the day (E1) and the date (E2) but so that it only auto fills for Mondays, Wednesdays, Fridays and Sundays for every week in the month? I have done the below manually to illustrate what I want.

Many thanks in advance to anyone who is able to help me.

Book3
ABCDEFGHIJK
1Month10
2Year2024
3WednesdayFridaySundayMondayWednesdayFridaySundayMondayWednesday
4First NameSurname02/10/202404/10/202406/10/202407/10/202409/10/202411/10/202413/10/202414/10/202416/10/2024
5JoeBloggs
6SandySmith
7BartSimpson
8MickeyMouse
Sheet1
Click to expand...
To adjust the number of columns that you need for the month just drag the formulas in cells D3:D4 across to the right.

Automatically generate dates for specific days of the week on any given month.xlsm
ABCDEFGHIJ
1Month1
2Year2024
3MondayWednesdayFridaySundayMondayWednesdayFridaySunday
4First NameSurname01/01/202403/01/202405/01/202407/01/202408/01/202410/01/202412/01/202414/01/2024
5JoeBloggs
6SandySmith
7BartSimpson
8MickeyMouse
9
January 2024
Cell Formulas
RangeFormula
C3:J3C3=TEXT(C4,"DDDD")
C4C4=LET(d,DATE(E2,E1,1),MIN(d+7-WEEKDAY(d+5),d+7-WEEKDAY(d+3),d+7-WEEKDAY(d+1),d+7-WEEKDAY(d+6)))
D4:J4D4=LET(d,C$4+INDEX({1,2,5,2,0,2},,WEEKDAY(C$4)),d)
 
Upvote 0
Solution
Put this formula in cell C3 and don't copy anywhere. Format row 3 with a Custom Format of "dddd" and format row 4 with your desired date format

24 10 21.xlsm
ABCDEFGHIJKLMNOPQRST
1Month10
2Year2024
3WednesdayFridaySundayMondayWednesdayFridaySundayMondayWednesdayFridaySundayMondayWednesdayFridaySundayMondayWednesday
4First NameSurname2/10/20244/10/20246/10/20247/10/20249/10/202411/10/202413/10/202414/10/202416/10/202418/10/202420/10/202421/10/202423/10/202425/10/202427/10/202428/10/202430/10/2024
Practice Days
Cell Formulas
RangeFormula
C3:S4C3=LET(a,WORKDAY.INTL(DATE(E2,E1,0),SEQUENCE(,31),"0101010"),d,FILTER(a,MONTH(a)=E1),VSTACK(d,d))
Dynamic array formulas.
 
Upvote 0
Works for Excel 2010 or later
You can use WORKDAY.INTL(Start_Date,number of days,"0101010") where "0" indicates Mon,Wed,Fri and Sun
Book1
ABCDEFGHIJKLMNOPQRS
1Month10
2Year2024
3WednesdayFridaySundayMondayWednesdayFridaySundayMondayWednesdayFridaySundayMondayWednesdayFridaySundayMondayWednesday
4First NameSurname02/10/202404/10/202406/10/202407/10/202409/10/202411/10/202413/10/202414/10/202416/10/202418/10/202420/10/202421/10/202423/10/202425/10/202427/10/202428/10/202430/10/2024
5JoeBloggs
6SandySmith
7BartSimpson
8MickeyMouse
Sheet1
Cell Formulas
RangeFormula
C3:S3C3=C4
C4C4=WORKDAY.INTL(DATE($E$2,$E$1,0),1,"0101010")
D4:S4D4=WORKDAY.INTL(C$4,1,"0101010")
 
Upvote 0
bebo021999 said:
Works for Excel 2010 or later
Click to expand...
The OP has 365 so a spill formula is probably fine, but if it was required for earlier versions and trying to accommodate the OP's wish for this to be as automated as possible then we could save them having to manually drag across or delete excess formulas at the right from month to monthas follows.
C3 and D4 formulas are copied across at least far enough to accommodate the most practice days possible in a month then all that needs doing each month is to alter E1/E2 as required.

24 10 21.xlsm
ABCDEFGHIJKLMNOPQRSTU
1Month10
2Year2024
3WednesdayFridaySundayMondayWednesdayFridaySundayMondayWednesdayFridaySundayMondayWednesdayFridaySundayMondayWednesday  
4First NameSurname2/10/20244/10/20246/10/20247/10/20249/10/202411/10/202413/10/202414/10/202416/10/202418/10/202420/10/202421/10/202423/10/202425/10/202427/10/202428/10/202430/10/2024  
Practice Days (2)
Cell Formulas
RangeFormula
C3:U3C3=C4
C4C4=WORKDAY.INTL(DATE(E2,E1,0),1,"0101010")
D4:U4D4=IF(C4="","",IF(MONTH(WORKDAY.INTL(C4,1,"0101010"))=$E1,WORKDAY.INTL(C4,1,"0101010"),""))
 
Upvote 0
Wow! Thank you all so much for taking the time to respond. I was working on replicating the first solution, which I managed to do and then noticed that additional solutions were also offered! I will try the other solutions too - but thank you all so much.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Is there a quick way to create / fill this list
2 3
Replies
20
Views
379
Eric W
Eric W
Z
  • Question Question
Sum Sales Values by Dates over different periods
Replies
6
Views
496
RobP
R
Z
  • Solved
add number of working days to a specific date (Start Date) to have the (End Date)
Replies
5
Views
292
Peter_SSs
Peter_SSs
A
  • Question Question
Run Macros at Specific Date and Time
Replies
5
Views
110
AV_Geek
A
C
  • Question Question
Analysing Data According to day of the week.
Replies
5
Views
135
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,223,868
Messages
6,175,084
Members
452,611
Latest member
bls2024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top