Academic / Financial year planner for Excel
He’s done it again, David Peel has quickly made up a new version of his Excel Year Planner that starts the year from any month you choose. Great for academic years (start in August) or financial years (e.g. Aussie start in July).
The year selector is top left of the main worksheet. Change the starting month from the pull-down list on the ‘Setting and Lists’ tab.
Download Financial – Academic Year Planner.xlsx
Right-click on the above link then choose ‘Save link as …’ or similar option (the exact wording depends on your browser).
It’s an Excel ‘macro free’ xlsx file with no external data connections. Naturally, we’ve checked it for viruses etc. but you should NEVER take anyone’s word for that <sigh>.
The worksheet should work in all modern Excels; Excel 365 for Windows and Mac. Excel app for Android, Excel app for Apple iPad/ iPhone and Excel Online, the browser based version of Excel.
How the Financial / Academic Year Planner works
This calendar is essentially the same as the calendar year planner, also available as a download.
This version calculates the months in Col A instead of using fixed months. In both worksheets Col A has dates as Excel serial dates though only the month is used to display and calculate the day cells.
The first month (A2) is calculated like this:
=DATE(@FinYear,VLOOKUP(StartMonth,MonthListRange,2,FALSE),1)
@FinYear is a Name Range for the selected year. It cleverly uses a table reference to grab the header from the main table:
=FinYearTable[[#Headers],[2020]]
Vlookup – gets the month number for the selected start month from the MonthListRange . Vlookup() is preferable to the newer Xlookup() for wider compatibility.
1 — since the day doesn’t matter, just 1 (1st of the month) is enough.
The other months in Col A use Edate() to add a month. The second month Cell A3 is =EDATE(A2,1) Edate has been around since Excel 2007, before that function, longer formulas were needed to reliably add/subtract months from Excel serial dates.
Non-existent days
The impossible days at the end of some months (like 31st September) use a slightly different Conditional Formatting test.
=AD2>EOMONTH($A2,0)
EoMonth() is another useful Excel date function which returns the last day for the month.
Calendar as Excel Table
The whole year planner is a table called FinYearTable.
One advantage of a table is that the table headers can replace the column numbers when you scroll down. This is a little mentioned addition to Excel 365.
Additions and Improvements
Most, if not all, the enhancements detailed in Year Planner calendar, Improvements and Extensions can be used with the Academic / Financial Year Planner.
See: Yearly Planner / Calendar for Office Watch readers
Year Planner calendar, Improvements and Extensions
Outlook Print Calendar Options -a Daily/Weekly/Monthly plan on paper
2020 calendars in Word and the tricks to make them special