Academic / Financial year planner for Excel


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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).

academic financial year planner for excel microsoft office 33969 - Academic / Financial year planner for Excel

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.

academic financial year planner for excel microsoft office 33970 - Academic / Financial year planner for Excel

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:

academic financial year planner for excel microsoft office 33971 - Academic / Financial year planner for Excel

=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]]

academic financial year planner for excel microsoft office 33972 - Academic / Financial year planner for Excel

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.

academic financial year planner for excel microsoft office 33973 - Academic / Financial year planner for Excel

=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.

academic financial year planner for excel microsoft office 33974 - Academic / Financial year planner for Excel

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 PowerPoint

2020 calendars in Word and the tricks to make them special

subs profile e1563205311409 - Academic / Financial year planner for Excel
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address