Compare a date between two dates and return correct year

[ELPC]

Hi all, 

My company has a fiscal year from July 1st to June 30th instead of the regular calendar year.  I have a date and I want to compare it to a listing of dates and return the correct fiscal year.  For example, I want to compare the date "7/25/19" to the Mapping table below and if it found between the two dates, it should return the Fiscal year field which would be "2020".  I can put in a lot of embedded If statements but I'm assuming there must be a better way.  Thanks for any help.  

Data

4/25/19

7/21/19

11/22/20

5/23/18

1/5/19

Mapping table

Fiscal year        Start Date       End Date

2019                  7/1/18             6/30/19

2020                  7/1/19             6/30/20 

2021                  7/1/20             6/30/21 

Result wanted

Date             Fiscal Year

4/25/19          2019

7/21/19          2020

11/22/20        2021

8/23/18          2019

1/5/19            2019

[MarqueeCrew · Accepted solution]

@ELPC ,

There are some data issues in you example... but here's my take:

1. DATE/TIME TOOL:  MM/dd/yy is used to convert your incoming dates
2. FORMULA TOOL:  Use this formula to calculate FY

IF ToNumber(substring([DateTime_Out],5,2)) >= 7 THEN ToNumber(left([DateTime_Out],4)) +1
ELSE ToNumber(Left(DateTime_Out,4))
ENDIF

Cheers,

Mark 

[ELPC]

Brilliant!  I knew there was a much easier way.  Thank you!  

[DiganP]

@ELPC Not as compact as @MarqueeCrew's but still gets the job done! Attached is the workflow.