# Custom Fiscal Year - help with Oct, Nov and Dec

Options
This discussion was created from comments split from: Bespoke year / fiscal year rather than calendar year?.

• Options

@Dan Palenchar or whoever can assist!

I've been hanging onto the above advice ready to go for it and go through the entire system to convert from calendar to fiscal. Need some help though!

Formula is returning 2024 instead of 2023, it is May 19th 2024 today.

1. This Year Start: =IF(MONTH(TODAY()) > 9, DATE(YEAR(TODAY()), 10, 1), DATE(YEAR(TODAY() - 1), 10, 1))

Returning 01/10/2024 (1st October 2024)

it should be

01/10/2023 (1st October 2023)

For the months of October, Nov and December it would change to 2024 as the next fiscal year starts 1st October.

I'm a UK user, not sure if there are any differences.

Regards Michael

Options

For the months that are "greater than 9" (so Oct, Nov, Dec), the DATE portion is returning Today's Year. Instead, you can say "Today's Year minus 1", or last year.

Try this, reversing the 2 Year statements:

=IF(MONTH(TODAY()) > 9, DATE(YEAR(TODAY()) -1, 10, 1), DATE(YEAR(TODAY()), 10, 1))

• Options

Thank you for coming back to me, its not delivering the desired result. Attached is a jpeg.

Formula currently returns 01/10/24 Grey arrow.
(You can see I have manually typed in 01/10/23) above it to check the rest of the formula works.

The original formula makes sense to me but it returns 2024 not 2023.

This Year Start: =IF(MONTH(TODAY()) > 9, DATE(YEAR(TODAY()), 10, 1), DATE(YEAR(TODAY() - 1), 10, 1))

This I think is saying if its

Oct, Nov or Dec of this year it will return 1st October this year

Otherwise it return 1st October last year? (The months of Jan to Sept)

Mike

Options

My apologies, I misunderstood what you were looking to do, this explanation is very helpful.

You're correct, the current formula should be the one that works.

Since today's month is 5, it should default to the last statement, which is what you want. However I've found the culprit! We're subtracting 1 from TODAY, not from the YEAR. (So it's getting the Year from yesterday, instead of last Year).

Change:

DATE(YEAR(TODAY() - 1), 10, 1))

To:

DATE(YEAR(TODAY()) - 1, 10, 1))

That should do the trick!

Cheers,
Genevieve

• Options

Definitely was a a culprit for sure!