Automatic Calendar Spreadsheet
I have made a number of flyers over the years with a calendar somewhere on them. Often times these calendars need to be updated as the flyer gets reprinted. This always was a pain as the dates had to be entered manually again.
I figured out a way to do this automatically. It doesn't take the events on the calendar as well, but at least the dates are updated.
This calendar is designed to update weekly, moving the dates along week by week.
To do this you will need seven columns with the first row of each labeled for the days of the week.
Now in the first day enter the below formula.
=DAY(TODAY()-WEEKDAY(TODAY(),1)+1)
This formula takes today's date, subtracts the day of the week (formatted as a number), adds one (I will explain in a second), and then pulls the day of the month out. This is the date for the first day of the first week. We had to add 1 because the WEEKDAY() function counts the first day of the week as 1 which then gets subtracted out giving us the end of the last week.
This formula counts Sunday as the first day of the week. If you prefer Monday as the first replace the 1 in the WEEKDAY() function with a 2. This is the first 1 in the formula, not the +1 at the end.
Now in the cell to the right enter the below formula.
=IF(A3+1<=DAY(EOMONTH(TODAY(),0)),A3+1,1)
This one is a little more complicated, but not that much. If first checks to see if the day represented by this cell is not after the last day of the month. It does this by taking today's date, finding the last day of the month, then comparing it to the day from the previous cell plus one. If it is not after the end of the month it simply displays the day from the previous cell plus one. If it is after the last of the month it simply displays 1. Make sure that where it says "A3" the formula is pointing to the previous cell.
Now autofill this formula across to fill out the week. Autofill all of these cells down as many weeks as you want. Then fill across to the left to get the first of each week. You will see a bunch of errors. Don't worry. In the first day of the second week make the formula (where it says "A3" above) point to the last day of the first week. Now autofill that down.
Now you have a calendar that will automatically update week by week when you open the file.
Unfortunately getting the name of the month varies from program to program. In Google Sheets use
=text(TODAY()-WEEKDAY(TODAY())+7,"MMMM")
But I can't be sure this will work in your program of choice.
These functions may need some tweaking, I haven't tested these in every spreadsheet program nor actually tested it against multiple dates (I just wrote it tonight). But I will update this post as I learn more.
I figured out a way to do this automatically. It doesn't take the events on the calendar as well, but at least the dates are updated.
This calendar is designed to update weekly, moving the dates along week by week.
To do this you will need seven columns with the first row of each labeled for the days of the week.
Now in the first day enter the below formula.
=DAY(TODAY()-WEEKDAY(TODAY(),1)+1)
This formula takes today's date, subtracts the day of the week (formatted as a number), adds one (I will explain in a second), and then pulls the day of the month out. This is the date for the first day of the first week. We had to add 1 because the WEEKDAY() function counts the first day of the week as 1 which then gets subtracted out giving us the end of the last week.
This formula counts Sunday as the first day of the week. If you prefer Monday as the first replace the 1 in the WEEKDAY() function with a 2. This is the first 1 in the formula, not the +1 at the end.
Now in the cell to the right enter the below formula.
=IF(A3+1<=DAY(EOMONTH(TODAY(),0)),A3+1,1)
This one is a little more complicated, but not that much. If first checks to see if the day represented by this cell is not after the last day of the month. It does this by taking today's date, finding the last day of the month, then comparing it to the day from the previous cell plus one. If it is not after the end of the month it simply displays the day from the previous cell plus one. If it is after the last of the month it simply displays 1. Make sure that where it says "A3" the formula is pointing to the previous cell.
Now autofill this formula across to fill out the week. Autofill all of these cells down as many weeks as you want. Then fill across to the left to get the first of each week. You will see a bunch of errors. Don't worry. In the first day of the second week make the formula (where it says "A3" above) point to the last day of the first week. Now autofill that down.
Now you have a calendar that will automatically update week by week when you open the file.
Unfortunately getting the name of the month varies from program to program. In Google Sheets use
=text(TODAY()-WEEKDAY(TODAY())+7,"MMMM")
But I can't be sure this will work in your program of choice.
These functions may need some tweaking, I haven't tested these in every spreadsheet program nor actually tested it against multiple dates (I just wrote it tonight). But I will update this post as I learn more.
Comments