Auto-populate important dates into Numbers Calendar

Question

Level 1

4 points

Auto-populate important dates into Numbers Calendar

Hello,

I have a calendar template in Numbers that I print at the beginning of each year. I'm looking for a way to populate important dates in the calendar based on a list of dates in a separate sheet.

The screenshot below illustrates what I'm trying to do.

The formula in I3 would need to ask something to the effect of "if the day and month of H3 matches the day and month of an item in Important Dates B:B then show the text in the corresponding row of Important Dates A:A".

Just not sure how to write that formula. Thank you for any help.

Posted on Dec 31, 2022 1:57 AM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

36,134 points

Dec 31, 2022 7:27 AM in response to mttwdp

If you enter "dates" in column B of Important Dates as text (left aligned by default) and not as date-time then you can do a simple lookup like this:

=XLOOKUP(J2,IMPORTANT DATES::B,IMPORTANT DATES::A,"")

For this method you would add a lookup column in the calendar that you can then hide. That column would include the "text date" in a format that matches the format you are using in column B of Important Dates.

It isn't clear from your screenshot, but it looks as if you have just a number in column H. If that is the case, then you could generate the matching "text dates" with a formula like ="Jan "&H2 filled down through the rest of the January cells, and similar for February, etc.

SG

Reply

Answer 2

mttwdp Author

Level 1

4 points

Jan 2, 2023 2:24 AM in response to SGIII

Thank you SG. That was helpful. I modified your solution and made it work.

The numbers on the calendar are actually the full date, so the problem I had with XLOOKUP is that years wouldn't match. For example, Joe's Birthday in important dates might be listed as 1/6/1988, which means it didn't match with 1/6/2023 on the calendar.

To solve the problem I created a hidden column in the Important Dates table that converts each important date to the current year. Here's an example of the formula: "=DATE(2023, MONTH(B1), DAY(B1))". Then I use that column for the XLOOKUP and it works great.

Reply