# Excel day planner - two questions



## Mandy_ (Dec 15, 2022)

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_ (Dec 26, 2022)

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


----------



## Dave Patton (Dec 27, 2022)

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.xlsmABCDEFGHIJKLMNO1Jan 23Calendar A to WFeb 232MonTueWedThursFriSatSunMonTueWedThursFriSatSun3 1 12345423456786789101112591011121314151314151617181961617181920212220212223242526723242526272829272883031910Apr 23May 2311MonTueWedThursFriSatSunMonTueWedThursFriSatSun12 1-Apr-232-Apr-23########234567133-Apr-234-Apr-235-Apr-236-Apr-237-Apr-238-Apr-239-Apr-238910111213141410-Apr-2311-Apr-2312-Apr-2313-Apr-2314-Apr-2315-Apr-2316-Apr-23151617181920211517-Apr-2318-Apr-2319-Apr-2320-Apr-2321-Apr-2322-Apr-2323-Apr-23222324252627281624-Apr-2325-Apr-2326-Apr-2327-Apr-2328-Apr-2329-Apr-2330-Apr-2329303117181bCell FormulasRangeFormulaA2:G2,I11:O11,A11:G11,I2:O2A2=TEXTSPLIT({"Mon","Tue","Wed","Thurs","Fri","Sat","Sun"},1)A3:G8,I12:O17,I3:O8A3=LET(dt,SEQUENCE(6,7,WORKDAY.INTL(A1+1,-1,"0111111"),1),IF(dt-DAY(dt)+1=A1,DAY(dt),""))A12:G17A12=LET(dt,SEQUENCE(6,7,WORKDAY.INTL(A10+1,-1,"0111111"),1),IF(dt-DAY(dt)+1=A10,dt,""))Dynamic array formulas.


----------



## Dave Patton (Dec 27, 2022)

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

Calendar_2023.xlsmABCDEFG12December 20233Dec_23Cell FormulasRangeFormulaD2D2=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.xlsmABCDEFG1Year20222MonthDecDecember 202234MondayTuesdayWednesdayThursdayFridaySaturdaySunday5 12346567891011712131415161718819202122232425926272829303110ISO Standard 8601Cell FormulasRangeFormulaD2D2=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,DAY(dt),""))Dynamic array formulas.


----------



## Mandy_ (Dec 30, 2022)

Thank you for your help


----------

