Calculate fiscal year based on today's date

[justinkgreen]

For the life of me, I can't figure out how to calculate the current fiscal year (e.g., 2021) based on today's date.

For context, I have customers whose fiscal years begin in different months. Based on today's date (dynamically changes), I need to calculate their current fiscal year.

For example, if their fiscal year begins in January, based on today's date (Aug. 26, 2021), they are in 2021. If their year begins in October, they are also in 2021 (as of today's date).

If you look at the attached doc, my formula is flawed and doesn't reflect this. Please help!

[queuesef]

A year has 12 periods. Depending on when the Fiscal year MONTH starts is when the year begins. Each month is given 1 through 12. So fiscal year starting in June (6/1/2021 ~ 5/31/22) would be Period 2 if you are in August 2021 [Jun= period 1; July = Period 2; Aug = 3.l..]
I believe for the October starting period the year would be 2021; period 1 = Oct '20; Per 2 = Nov 20; Per 3 = Dec 20; Per 4 = Jan 2021.....I generally start with their starting month and go out 12 months.... a June start would start: 6/1/2020 and go through 5/31/21. I look at it as 'the year going into, ONLY if it is NOT calendar year (Jan ~ Dec).
I hope this helps. [PS i changed your formula in cell B1 to "=IF((A2)=1,YEAR($D$2),YEAR($D$2)+1) " and copied it down. if you're fiscal is not Calendar, then you're in the next year]

[Ochimus]

As shown on attached,

Set C3 as Custom Format to show Month and Days only

Set D3 as Short Date, enter this in D3 and copy down:
=IF(B$3>D5,YEAR(B$3),YEAR(B$3)+1)

Set Col E as Number, put this in E3 and copy down:
=IF(B$3>D3,YEAR(B$3),YEAR(B$3)+1)

So if the client's Fiscal Year starts on 1 Jan and your date is 30 June, you are in the same year (Rows 3 and 5). But if their Year starts in November, the Fiscal Year is still 2020 (Row 4)

Hope this helps

Ochimus

[justinkgreen]

A year has 12 periods. Depending on when the Fiscal year MONTH starts is when the year begins. Each month is given 1 through 12. So fiscal year starting in June (6/1/2021 ~ 5/31/22) would be Period 2 if you are in August 2021 [Jun= period 1; July = Period 2; Aug = 3.l..]
I believe for the October starting period the year would be 2021; period 1 = Oct '20; Per 2 = Nov 20; Per 3 = Dec 20; Per 4 = Jan 2021.....I generally start with their starting month and go out 12 months.... a June start would start: 6/1/2020 and go through 5/31/21. I look at it as 'the year going into, ONLY if it is NOT calendar year (Jan ~ Dec).
I hope this helps. [PS i changed your formula in cell B1 to "=IF((A2)=1,YEAR($D$2),YEAR($D$2)+1) " and copied it down. if you're fiscal is not Calendar, then you're in the next year]

Thank you for this. When I copy down your formula, I noticed the following:

1. When the fiscal year begins in January, it shows the current fiscal year is 2022 (based on today's date). This should be 2021. The name of the fiscal year (2021, 2022, etc.) is always based on the date of the last month. So for Jan-Dec FYs, the current FY is 2021 (based on today's date). For Feb-Jan FYs, the current FY is 2022. Etc.

2. The second thing I noticed is the value current fiscal year value for a September FY start comes back as 2020. Companies with a Sep-Aug FY would currently be in 2021 (date of the final month per my note above).

Please let me know if I need to provide a better explanation.

[justinkgreen]

Thank you as well Ochimus! Here's what I found when I used the formulas you suggested:

The name of the fiscal year (2021, 2022, etc.) is always based on the date of the last month. So for Jan-Dec FYs, the current FY is 2021 (based on today's date) because the last month is Dec 2021. For Feb-Jan FYs, the current FY is 2022 because the last month is Jan 2022. Etc.

Using your formulas, I noticed that (based on today's date) fiscal years beginning in Sep, Oct, Nov and Dec come back as 2021 but should be 2022 (because of when they end).

So basically, any FY that begins in Feb or later will have a name that is the current year (2021) + 1 (= 2022). For FYs beginning in Jan, the current year will be 2021 (based on today's date).

My apologies if this is confusing the matter. Just let me know if I can clarify.

[justinkgreen]

CORRECTION: Sorry queuesef, my mistake. Your formula looks to be working as expected! Let me stress test this a bit more but I think this should do the trick!

[justinkgreen]

Hi queuesef - there was a minor issue but I believe I've solved for it.

Your formula was returning "2022" for fiscal years beginning in Sep, Oct, Nov, Dec (based on today's date). So I modified it with an IF statement:
=IF(A2>MONTH($D$2),IF((A2)=1,YEAR($D$2),YEAR($D$2)),IF((A2)=1,YEAR($D$2),YEAR($D$2)+1))

If the FY start month is greater than the current month, then return the current year. Otherwise, return the current year + 1.

Thank you for all your help!