Excel calendar

[cougan]

Using excel and a calendar template, how can you make birthdays and other yearly events move to the correct day when the calendar changes to the new year? I have looked at several templates and looked a lot online and cannot find the answer. All the templates will automatically move days for the new year, but none of them will move birthdays (for example) to the correct day. I am sure you can apply a formula of some kind that will also make the data you enter on March Tuesday 5th, for example,  automatically move that data to March Wednesday 5th the next year, or at least I hope so. 

[#2]

@cougan I suppose your calendar has a cell that contains the year number somewhere. Let's give that cell a name, for example, "CalYear". Else select that cell and look in the Name Box. It might already have a name.

Then, on a separate tab in the workbook you probably have a list of events, birthdays and other aniversaries with a data column and a description. Then I would start that list with all the recurring events, but rather than just entering a data like 1/1/2023 for New Year's Day, enter a formula:

=DATE(CalYear,1,1).

That will display the date for January 1, yyyy (or whatever local format you have chosen for dates) where the year depends on the year number in the cell called "CalYear".

[#3]

Thank you. I am not sure that I understand your instructions. Is there a way for me to send my calendar file so you can see what I am specifically dealing with? @Riny_van_Eekelen Here is a link to the file. https://1drv.ms/x/s!Aq8EGDbKG31RhdB4hZzWn7zJGhEilA?e=sfzxRb 

Cougan

[#4]

@cougan Oh, I see. The template in the link is totally useless. It replaces a pen and paper calendar that you have to re-write every year. All it does is re-align the dates with the correct days of the week when you change the year. Thus, a total waste of time. Just scrap it!

A year ago or so, I answered a question in this forum regarding another useless calendar template. Though it had some time-intelligence built in. I modified it and others have started using and modifying my modifications.

Now, I stripped it even further to show you what I had in mind.  An Events table where you list all recurring and one-off events. These get displayed in a dynamic monthly view. I trust you will be able to follow my previous instructions. The attached file contains a few example events.

[#5]

I can see the potential of that one and appreciate you sharing it with me. Is there not a way that you can just put the information directly on the calendar without having to have a separate tab? If not, that is ok, but that is what I was hoping for.

[#6]

@cougan 

Short answer:

No, you can't have it without a separate event list.

Slightly longer answer: 

This is what the developer (Vertex42) writes on their web-site:

"Quickly print a blank yearly 2023 calendar for your fridge, desk, planner or wall using one of our PDFs or Images."

It's designed to be used in the printed version and notes are to be written on it with a pen. Nothing more, nothing less. The only advantage you have with the Excel template is that you don't have to buy a new calendar next year. Change the year and print a new one and copy the recurring events manually. And that what you have to do as well when you choose to enter events in the Excel sheet.

[#7]

I have been trying to create one that works easier that would work with the one I posted because I like the way it prints out. However, I have failed miserably at trying to get it to work.