Excel day planner - two questions

[Mandy_]

Hi,

I am creating a day planner. I'll include screen shots, sorry I can't put up the excel file at the moment.

I have two sheets. Sheet one is the first three pages of the planner (index/year of dates page, grocery list and main to do list). I have hyperlinks down the bottom and these links are working.
Sheet two is the day planners themselves. Where the date is at the top of the page.

I have a worksheet (fourth page) where the dates are calculated from an array
=CHOOSE({1;2;2;2;2;2;2},{"Mon","Tue","Wed","Thurs","Fri","Sat","Sun"},IF(MONTH(DATE(YEAR(AH3),MONTH(AH3),SEQUENCE(7,7,AL4,1)))=MONTH(AH3),SEQUENCE(7,7,AL4,1),""))

For the index page (where the value of the array calculation is copied to), my intention is to click in the date and I am taken to the day planner's day.
This is the formula I have for the hyperlinked cells on the index page for the date of June 30 2023:
=HYPERLINK("#'"&MID(CELL("ADDRESS",INDEX('Part Two - day planners'!$A:$A,MATCH('Take Two First Three Pages'!AT30,'Part Two - day planners'!$A:$A,0),0)),FIND("]",CELL("ADDRESS",INDEX('Part Two - day planners'!$A:$A,MATCH('Take Two First Three Pages'!AT30,'Part Two - day planners'!$A:$A,0),0)))+1,100),'Take Two First Three Pages'!AT30)

BUT - First question - when I click the hyperlink the cell I am taken to is the same cell each month. Say for May 11th 2023, I am taken to cell $A$353, and for July 11th 2023, I am taken to the same cell $A$353. So something is not right in my hyperlink formula, but I can't work out where.

Second question - for the day planner dates - I have the first date copied from the cells in the original array (copied from the working page) and the date copied is displaying as 1/1/1900
How do I change this to be current/the date that is displayed in the array calculation? All I have done is copy the value in the cell. I've tried formatting the cell and tried changing to '1904 date system' but that messes every date!

Sorry I can't include the excel file at the moment.

Thank you so much for your help.

 

[Mandy_]

I'd appreciate any help here - or who I can ask for help? Thank you.

 

[Dave Patton]

Do you want fixed hyperlinks or hyperlinks generated by the click.
How many Daily sheets will you have? 365? Do you require an index for your workbook and could that sheet provide the hyperlinks to each page.
What is in the sheet references for the formula that you mentioned?
Does that formula work for years other than 2023?
Does the formula provide the details for all months and do you have the full date for each date? See the different results for Jan and April
What hyperlinks are actually working?

The images are not nearly as helpful as an extract with the forum's tool named XL2BB,

What is the name of the sheet with the Jan 1 2023 date?
The date could be calculated from the sheet name. see the next post.

Daily Diary 2023.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Jan 23							Calendar A to W	Feb 23
2	Mon	Tue	Wed	Thurs	Fri	Sat	Sun		Mon	Tue	Wed	Thurs	Fri	Sat	Sun
3							1				1	2	3	4	5
4	2	3	4	5	6	7	8		6	7	8	9	10	11	12
5	9	10	11	12	13	14	15		13	14	15	16	17	18	19
6	16	17	18	19	20	21	22		20	21	22	23	24	25	26
7	23	24	25	26	27	28	29		27	28
8	30	31
9
10	Apr 23								May 23
11	Mon	Tue	Wed	Thurs	Fri	Sat	Sun		Mon	Tue	Wed	Thurs	Fri	Sat	Sun
12						1-Apr-23	2-Apr-23		########	2	3	4	5	6	7
13	3-Apr-23	4-Apr-23	5-Apr-23	6-Apr-23	7-Apr-23	8-Apr-23	9-Apr-23		8	9	10	11	12	13	14
14	10-Apr-23	11-Apr-23	12-Apr-23	13-Apr-23	14-Apr-23	15-Apr-23	16-Apr-23		15	16	17	18	19	20	21
15	17-Apr-23	18-Apr-23	19-Apr-23	20-Apr-23	21-Apr-23	22-Apr-23	23-Apr-23		22	23	24	25	26	27	28
16	24-Apr-23	25-Apr-23	26-Apr-23	27-Apr-23	28-Apr-23	29-Apr-23	30-Apr-23		29	30	31
17
18
1b

Cell Formulas
Range	Formula
A2:G2,I11:O11,A11:G11,I2:O2	A2	=TEXTSPLIT({"Mon","Tue","Wed","Thurs","Fri","Sat","Sun"},1)
A3:G8,I12:O17,I3:O8	A3	=LET(dt,SEQUENCE(6,7,WORKDAY.INTL(A1+1,-1,"0111111"),1),IF(dt-DAY(dt)+1=A1,DAY(dt),""))
A12:G17	A12	=LET(dt,SEQUENCE(6,7,WORKDAY.INTL(A10+1,-1,"0111111"),1),IF(dt-DAY(dt)+1=A10,dt,""))
Dynamic array formulas.

 

[Dave Patton]

Date in a cell is build from the sheet's name.

Calendar_2023.xlsm
	A	B	C	D	E	F	G
1
2				December 2023
3
Dec_23

Cell Formulas
Range	Formula
D2	D2	=LET(s,LET(n,CELL("filename",A1),MID(n,FIND("]",n)+1,99)),DATE("20"&RIGHT(s,2),MONTH(--(1&LEFT(s,3))),1))

Sheet Mon - Sun This can be converted for Sun - Sat Calendars.

Calendar_Examples.xlsm
	A	B	C	D	E	F	G
1	Year	2022
2	Month	Dec		December 2022
3
4	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
5				1	2	3	4
6	5	6	7	8	9	10	11
7	12	13	14	15	16	17	18
8	19	20	21	22	23	24	25
9	26	27	28	29	30	31
10
ISO Standard 8601

Cell Formulas
Range	Formula
D2	D2	=DATE(B1,MONTH(--(1&B2)),1)
A5:G10	A5	=LET(dt,SEQUENCE(6,7,WORKDAY.INTL(D2+1,-1,"0111111"),1),IF(dt-DAY(dt)+1=D2,DAY(dt),""))
Dynamic array formulas.

 

[Mandy_]

Thank you for your help