Calendar formula failed

Question

I followed the instructions for creating an active calendar. everything worked until I got to the part where the formula for applying the correct dates to the day and year failed. I copied the formula exactly as it appeared in the video (I made a screen capture to be sure it was the same. why didn't it work? When I submitted it, nothing happened - not even an error message. I have MS 365 on an HP all-in-one.

=IFERROR(INDEX(C_DATES,MATCH(CONCATENATE(MONTH($B$3),"",ROW(B5)-ROW(B$4),"",COLUMN(B5)-COLUMN($A5)),C_KEY,0)),"")

Answer 1

Yes, but my array is better ;-) I watched the video and the creator makes making a calendar a lot harder than it needs to be given the amazing new array formulas implemented a few years ago.

I added support for holidays and weekends - holidays are green and weekends are grey.

Kevin

Answer 2

Thanks. I already have the array. I need the formula to get it into my calendar with the right start day and date for each month. this is the video I was watching: https://www.youtube.com/watch?v=hCfvqAiOxe0.

the problem begins at 17:19 to create the look up key. He gets hash tags, I get nothing.

thank you for putting that together. It's a great calendar. But I still don't know how to auto-fill the dates under each day and month from the lookup key.

Answer 3

Perhaps this will work for you:

https://www.dropbox.com/scl/fi/y84hlknw5wzkz5d59hknm/Year-Calendar.xlsx?rlkey=859kk6trj80mvzedwhm05bgk2&dl=0

Instead of a table of dates, I used a formula to calculate an array of days for each month.

Kevin

Answer 4

Well, of course, I can't find the same video I used when I started this. However, I did create, on another tab, the DATES (A1) [=DATE(I_YR,1,1)];

Week Number in the month (B1) [=B1]; and Lookup Key (C1) [=C1]. It covers the entire year. I am wondering if I misnamed one of these and input the wrong reference in the formula. I FOUND IT! https://www.youtube.com/watch?v=hCfvqAiOxe0

Answer 5

Can you post a link to the video?

How have you defined C_DATES and C_KEY?