Is there a way to set up fiscal months other than a giant IF statement

Kelly Kraft

We have our fiscal months that always end on a Saturday. So some months are Fiscal Month April 2024 will be from 4/1 - 5/3 = April, May is 5/4-5/31, etc. I made a Fiscal Months sheet with start date and end date for 3 years. I now want to cross reference that so when I import by revenue, bookings, etc. it will take the date and assign a fiscal month so I can then sum/analayze/graph etc. But the only way I know is to have formula like below. The problem is, each sheet I have to completely redo the ranges and selections. Is there a way to do this? Or do I just have to hard code the date instead of the range? As you can imagine, my formula right now has 72 ranges so its a lot of clicking. I need across multiple importing sheets.

=IF(Date@row < {Fiscal Months Range 1}, MONTH(Date@row), IF(AND(Date@row > {Fiscal Months Range 1}, Date@row < {Fiscal Months Range 2}), "4", IF(AND(Date@row > {Fiscal Months Range 3}, Date@row < {Fiscal Months Range 4}), "5", IF(AND(Date@row > {Fiscal Months Range 5}, Date@row < {Fiscal Months Range 6}), "6", IF(AND(Date@row > {Fiscal Months Range 7}, Date@row < {Fiscal Months Range 8}), "7", IF(AND(Date@row > {Fiscal Months Range 9}, Date@row < {Fiscal Months Range 10}), "8", IF(AND(Date@row > {Fiscal Months Range 11}, Date@row < {Fiscal Months Range 12}), "9", IF(AND(Date@row > {Fiscal Months Range 13}, Date@row < {Fiscal Months Range 14}), "10", IF(AND(Date@row > {Fiscal Months Range 15}, Date@row < {Fiscal Months Range 16}), "11",)

Scott Orsey

Hi @Kelly Kraft , I'm not certain I follow exactly what you've set up or how you are using it. In your Fiscal Months sheet, I assume you would have the following columns: [Fiscal Month], [First Day], [Last Day]. Then you would have a row for each month in ascending order, is that correct?

If so, then all you would need to do in your main sheet is to lookup the Date and return the Fiscal Month using INDEX(MATCH) like this:

= INDEX({Fiscal Month column from Fiscal Months}, MATCH(Date@row,{First Date column from Fiscal Months},1))

Note that the 1 in the MATCH() formula assumes that [First Day] is sorted in ascending order. Because it it looks up in ascending order, you don't technically need the [Last Day] column in your Fiscal Months sheet.

Would this accomplish what you want? Be well!

Kelly Kraft

Hi @Scott Orsey

Here is my Fiscal Months. So if I index match, I'd need to list out every date in that Fiscal Month? My data is uploaded very raw so it would say Sale #123 made on 8/6/22. And I've added a column so when I import the data, the formula will assign that that sale should count as Fiscal Month July.

Scott Orsey

Hi @Kelly Kraft , No, you don't need to list out every date. Your table should work with INDEX(MATCH()) with the [search type] set to 1, which is the default. MATCH() will return the index of the row that is the highest value less than or equal to the value you are searching for. (That's what I was trying to key in on in my explanation). So if you use the [Start Date] column you should get what you want. I think this will work for you.

Be well