Forum Discussion
Excel calendar
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.
6 Replies
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".
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
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.
