What it does
Use to create the week, month, quarter, and year beginning/ending dates for non-standard calendars by supplying a few inputs.

When to use it
For people that work at companies that do not follow the standard calendar schedule but use a non-traditional calendar (i.e. 4-4-5, 4-5-4, 5-4-4, 13×4, etc.), this can create the calendar for the next 30 years.

Why to use it
It dynamically builds a fiscal schedule using just a few inputs.  The schedule can be used for period/date lookups, distribute to team members for planning purposes, or use to upload/input into applications where dates are required.

Default shortcut
None

Other Details

• Category: Accounting / Calendars and Dates
• Difficulty: 3/5
• Usage/frequency: 1/5
• Automation factor: 5/5 (estimated 300 seconds saved each time used)
• Type: Bulk task
• Date added: 3/1/2022
• Tags: fiscal, calendar, periods, dates, accounting

Related Macros
Calculate Amortization Lines

Other Articles
None

Create Fiscal Calendar Example File

Prerequisites
Have available your previous fiscal period calendar to compare to.

Instructions
Run the Create Fiscal Calendar macro.  It will create several sheets:

1. Fiscal Period Inputs – this is where you’ll configure the type of calendar, start/end dates, etc. – see below.
2. Day – lists every day with end of week, month, quarter, and year.  This is often helpful for lookup purposes.
3. Week – lists every week with begin/end of week, month #, quarter #, year #, etc.
4. Month – lists the beginning and end of each month with days/weeks in month, quarter #, year #, etc.
5. Quarter – lists the beginning and end of each quarter, # of days/weeks in the quarter, year #, etc.
6. Year – lists the beginning and end of each year, # of days/weeks in the year, etc.

On the Fiscal Period Inputs sheet, fill in these inputs – the yellow boxes (use the example file or default inputs above as a guide):

1. First day of first fiscal year (required) – this is the date of the first day in the first fiscal year that should be used for all the output sheets.  The best practice is to start with a prior year (i.e. if it is 2018, start with 2017 for some history).  Use mm/dd/yyyy format.
2. Last day of first fiscal year (required) – this is the date of the last day in the first fiscal year that should be used for all the output sheets.  Use mm/dd/yyyy format.
3. First fiscal year label (required) – enter the label of the first fiscal year to use.
4. Fiscal calendar type (required) – select the calendar type from the drop-down list based on how many periods and weeks per period are in your fiscal calendar.  For the Custom type, see the instructions below.
5. Beginning month (required) – select the month in a standard calendar where the fiscal year begins.  For example, if your fiscal year runs from July to June, select 7 as the beginning month.  The ending month will automatically populate based on the beginning month.
6. Year-end day type (required) – select the type for how the year-end day is calculated.  There are two options: Last xxxday in month (i.e. the last Saturday of December) or Closest to date (i.e. closest Saturday to December 31).

Once the inputs are populated, review the checks in the green cells at the bottom.  These should be for 30 full fiscal years after the beginning dates.

When the fiscal calendar contains 13 4-week periods, review the quarter assignments in column O.  While most companies include 4 periods in quarter 4 of the year, this can be changed if desired using the quarter assignment values in column O.  

For custom fiscal calendar types, enter the period assignment for each week of the year in column W.  This will be leveraged by formulas throughout the workbook to determine how to group dates into periods, quarters, and years.