Categorize Data into Fiscal Year Based on Transaction Date

[HotDogHippo]

Hello,

I have a list of financial data that spans 3 fiscal years.

Fiscal year 2018 - April 1, 2018 - March 31st, 2019
Fiscal Year 2019 - April 1, 2019 - March 31st, 2020
Fiscal Year 2020 - April 1, 2020 - March 31st, 2021

The transaction date (YYYYMMDD) is in Column Q and wondering if it's possible to have a formula in Column R to identify which fiscal year the transaction falls into based on the above categories.

I.E.

Q R
Date of Transaction Fiscal Year
20180403 20182

Any help would be much appreciated...
Thanks!

20180403​

 

[Toadstool]

Hi HotDogHippo,

Does this work for you?

HotDogHippo.xlsx
	Q	R
1	Date	Fiscal Year
2	20170101	2016
3	20180403	2018
4	20190909	2019
5	20200401	2020
6	20200331	2019
Sheet1

Cell Formulas
Range	Formula
R2:R6	R2	=IF(MID(Q2,5,2)+0<4,LEFT(Q2,4)-1,LEFT(Q2,4))+0

 

[HotDogHippo]

Hi HotDogHippo,

Does this work for you?

HotDogHippo.xlsx
	Q	R
1	Date	Fiscal Year
2	20170101	2016
3	20180403	2018
4	20190909	2019
5	20200401	2020
6	20200331	2019
Sheet1

Cell Formulas
Range	Formula
R2:R6	R2	=IF(MID(Q2,5,2)+0<4,LEFT(Q2,4)-1,LEFT(Q2,4))+0

Hi Toadstool,

Amazing....That does work...are you able to walk me through the logic of why it works?? Many thanks

 

[Peter_SSs]

Amazing....That does work

In that case, this modified version of it should too I think.

21 05 20.xlsm
	Q	R
1	Date	Fiscal Year
2	20170101	2016
3	20180403	2018
4	20190909	2019
5	20200401	2020
6	20200331	2019
Fiscal Year

Cell Formulas
Range	Formula
R2:R6	R2	=LEFT(Q2,4)-(MID(Q2,5,2)-4<0)

 

[Toadstool]

Hi Toadstool,

Amazing....That does work...are you able to walk me through the logic of why it works?? Many thanks

You're welcome!
The +0 parts are to make sure the numbers are treated as numeric, not text.
MID(Q2,5,2) checks the date characters 5 and 6, the month. If the month is less than 4 (April) then the fiscal year is last year, so I take the first 4 characters (year) and subtract one.
If the month is 4 or higher then it's the same fiscal year as the date, so I take the first 4 characters and add zero to force numeric.