Filter Birthdays onto Dynamic Calendar

[mstark96]

Not sure how to go about this but I'm using a generic Microsoft Calendar template and have a list of names and date of births. What I'd like to do is have a filtered list on each calendar tab that will move with date changes and show the clients name and age on each tab and work as a birthday tracker. I've attached an example to help show what I'm doing. It should be pretty straightforward but let me know if you have any questions about what I'm trying to do.

Additionally, this is optional but I'd be incredibly grateful if someone could assist with it now but I'll eventually be adding more dated info in the future. If you know how to stack different filtered lists into one combined list that would be great. The info isn't tied to the DOB or anything else but like a regular calendar, it just get's added to the specific date.

Any help is appreciated!

[AliGW]

B4:

=LET(s,SEQUENCE(1,7,CHOOSE(WEEKDAY(DATE(YearToDisplay,MONTH(DATEVALUE(MonthToDisplay&" 1")),1)),1,0,-1,-2,-3,-4,-5))+IF(DayToStart="MONDAY",1,0),IF(s<1,"",s))

B10 and copy to other date rows:

=LET(s,SEQUENCE(1,7,H4+1,1),IF(s>DAY(EOMONTH(DATE(YearToDisplay,MONTH(DATEVALUE(MonthToDisplay&" 1")),1),0)),"",s))

B5 and copy to other detail cells:

=IFERROR(LET(f,FILTER('CURRENT TRST DSK ACCTS'!$B$5:$K$9,(MONTH('CURRENT TRST DSK ACCTS'!$J$5:$J$9)=MONTH(DATEVALUE(MonthToDisplay&" 1")))*(DAY('CURRENT TRST DSK ACCTS'!$J$5:$J$9)=Calendar!B4)),
INDEX(f,,1)&" - AGE "&INDEX(f,,10)),"")

You need to be more specific about how you want other details to appear. It's possible, but rows in these calendar 'cells' cannot increase. You might want to consider having the fully detailed list appear to the right of the calendar for either TODAY() or the whole month, as space in the 'cells' is limited.

[AliGW]

You could create a list on the side like this:

Please Login or Register  to view this content.

[AliGW]

You might need an error trap for this:

=IFERROR(LET(s,SEQUENCE(1,7,H4+1,1),IF(s>DAY(EOMONTH(DATE(YearToDisplay,MONTH(DATEVALUE(MonthToDisplay&" 1")),1),0)),"",s)),"")

[mstark96]

Thanks, after thinking about it and looking at both, the side list is probably best for the long term and easier to read/manage. Going off your formula I did some editing and added a second list based off the first for the bonus info. The only other thing if you could take a look again is if you select a different month like January or October the first day is cut off. This is easily fixed if you just set a different start day for the week but I wanted to see if you could look at it again potentially to help with that issue? I'm not familiar with how your formula works but I'll try and see if I can figure out how to fix it myself. Thanks again

[AliGW]

Change B4 to this:

=IFERROR(LET(s,SEQUENCE(1,7,CHOOSE(WEEKDAY(DATE(YearToDisplay,MONTH(DATEVALUE(MonthToDisplay&" 1")),1),2),1,0,-1,-2,-3,-4,-5)),IF(s<1,"",s)),"")

I have not tested thoroughly, but you should find it works. However, it won't work if you change the starting day of the week - are you likely to want to do this? If so, I'll have to factor that in for you.

[AliGW]

I think I have fixed it so that you can change the start day to whatever you want. I have tested it, but not for every single combination, so let me know if you find it fails at any point:

=LET(d,DATE(YearToDisplay,MONTH(DATEVALUE(MonthToDisplay&" 1")),1),IF(WEEKDAY(d,1)>=WEEKDAY(B3,1),SEQUENCE(1,7,d-IF(d=B3,0,CHOOSE(WEEKDAY(B3,1),6,5,4,3,2,1,0))),SEQUENCE(1,7,d-IF(d=B3,0,CHOOSE(WEEKDAY(B3,1),6,5,4,3,2,1,0))-1)))

I have added some CF to the top row of day numbers.

[AliGW]

And B10 needs to be:

=IFERROR(LET(s,SEQUENCE(1,7,DAY(H4)+1,1),IF(s>DAY(EOMONTH(DATE(YearToDisplay,MONTH(DATEVALUE(MonthToDisplay&" 1")),1),0)),"",s)),"")

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[HansDouwe]

Another solution:

Don't change B4, B11, B18, B25, B32 and B39. This remains as it was: {Index,calendar,ndx+0}, {Index,calendar,ndx+1}, and so on

And try in B5 and copy to C5:H5, B12:H12, B19:H19, B26:H26, B33:H33 and B39:H39:

Please Login or Register  to view this content.

[mstark96]

Thanks everyone, I was able to look at it again and it seems the best option was a mix of the two solutions. Thanks for your help!

[AliGW]

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.