Forum Discussion

Copper Contributor

Dec 26, 2022

Solved

Excel Day Planner - two questions

Hi, I am creating a day planner. I'll include screen shots. 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 ...

excel

dscheikey
Dec 26, 2022
Mandy_10
I hope I can answer a part of your questions. The reference to the cell
='Take Two First Three Pages'!AI6
gives a result of 1, because with the formula you use, you ultimately only generate numbers. To generate a date from this, you have to change it to:
=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), DATE(YEAR(AH3),MONTH(AH3),SEQUENCE(7,7,AL4,1)),"") )

dscheikey

Bronze Contributor

Dec 26, 2022

Mandy_10

I hope I can answer a part of your questions. The reference to the cell

='Take Two First Three Pages'!AI6

gives a result of 1, because with the formula you use, you ultimately only generate numbers. To generate a date from this, you have to change it to:

=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),
     DATE(YEAR(AH3),MONTH(AH3),SEQUENCE(7,7,AL4,1)),"")
)

Mandy_10
Copper Contributor
Dec 26, 2022
dscheikey

Thank you! This makes sense. But I couldn't work out what the array formula was giving the answer as. So thank you for explaining, and for your help with the formula.

Your fix actually fixed both of my questions! The hyperlinks now are correct also.

Thank you.