Nifty Excel date manipulation for fiscal years

[wrldwzrd89]

The attached image shows the output: what it all looks like when done.
The details:

• Cell A1 is the "base date" to build all other dates off of.
• Cell A2 is the fiscal year offset relative to the calendar year.
• (For this particular example, the fiscal year is 3 months ahead of the calendar year (+3).
• Cells C2 to N2 contain the offsets from the base date.
• Cells C1 to N1 have the dates themselves. These are powered by a rather complex formula (example is for cell C1):

=IF(C2>0,IF(AND(MONTH(DATE(YEAR($A$1),MONTH($A$1)+C2-1,DAY($A$1)))>12-$A$2,MONTH(DATE(YEAR($A$1),MONTH($A$1)+C2-1,DAY($A$1)))<=12),DATE(YEAR($A$1)+1,MONTH($A$1)+C2-1,DAY($A$1)),DATE(YEAR($A$1),MONTH($A$1)+C2-1,DAY($A$1))),IF(AND(MONTH(DATE(YEAR($A$1),MONTH($A$1)+C2,DAY($A$1)))>12-$A$2,MONTH(DATE(YEAR($A$1),MONTH($A$1)+C2,DAY($A$1)))<=12),DATE(YEAR($A$1)+1,MONTH($A$1)+C2,DAY($A$1)),DATE(YEAR($A$1),MONTH($A$1)+C2,DAY($A$1))))

To generate the formulas for D1 to N1 just fill C1 to the right.
The first half of the formula is necessary because the offsets in Cells I1 to N1 are 1 bigger than they should be to be "mathematically correct", so the formula needs to compensate for this. As for the second half (which is just the first half without that -1 offset), this part checks to see if the displayed year needs adjusting to account for the FY/CY difference - for this example, October through December need their year incremented by 1 due to the +3 offset. The remainder generates the date.

The only other gotcha is the custom number formatting I used in row 2 to make it look nice. The format code is:

+0;-0;0