automatic show start day and last day of each week when select week1, week2, ...

tendosai

New Member
Joined
Mar 14, 2022
Messages
26
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
I have table like below. however, doing the date manually is tough for every month. the date is shown from the start day of the week till last day of week (Mon to Sun). Example: below is week 1 so it will show like below (Mon and Tue not show any date because it was in Jan).

Week1
MondayTuesdayWednesdayThursdayFridaySaturdaySunday
1-Feb-232-Feb-233-Feb-234-Feb-235-Feb-23

so when change to Week2 it shows like below
Week2
MondayTuesdayWednesdayThursdayFridaySaturdaySunday
6-Feb-237-Feb-238-Feb-239-Feb-2310-Feb-2311-Feb-2312-Feb-23

same for week3 till week5 (base on our calendar)
i try to figure out using formular but it started to get complicated and going nowhere. it would be the best if everyone can help with VBA.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
You do not say how you specify the Date such as February 1 2023.
You can provide more information or try the following.

Calendar Examples 2.xlsm
ABCDEFG
1Year2023
2MonthFebFebruary 2023
3
4MondayTuesdayWednesdayThursdayFridaySaturdaySunday
5 1-Feb-232-Feb-233-Feb-234-Feb-235-Feb-23
66-Feb-237-Feb-238-Feb-239-Feb-2310-Feb-2311-Feb-2312-Feb-23
713-Feb-2314-Feb-2315-Feb-2316-Feb-2317-Feb-2318-Feb-2319-Feb-23
820-Feb-2321-Feb-2322-Feb-2323-Feb-2324-Feb-2325-Feb-2326-Feb-23
927-Feb-2328-Feb-23
10
ISO Standard 8601
Cell Formulas
RangeFormula
D2D2=DATE(B1,MONTH(--(1&B2)),1)
A5:G10A5=LET(dt,SEQUENCE(6,7,WORKDAY.INTL(D2+1,-1,"0111111"),1),IF(dt-DAY(dt)+1=D2,dt,""))
Dynamic array formulas.
 
Upvote 0
The following may also be relevant; it depends on your actual requirements.

Calendar Examples 2.xlsm
ABCDEFG
1Year2023
2MonthMarMarch 2023
3Week3
4MondayTuesdayWednesdayThursdayFridaySaturdaySunday
513-Mar-2314-Mar-2315-Mar-2316-Mar-2317-Mar-2318-Mar-2319-Mar-23
ISO Standard 8601 (3)
Cell Formulas
RangeFormula
D2D2=DATE(B1,MONTH(--(1&B2)),1)
A5:G5A5=LET(dt,SEQUENCE(1,7,WORKDAY.INTL(D2+LOOKUP(B3,{1,1;2,8;3,15;4,22;5,29;6,35}),-1,"0111111"),1),IF(dt-DAY(dt)+1=D2,dt,""))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,157
Messages
6,170,419
Members
452,325
Latest member
BlahQz

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