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
Solved! Go to Solution.
@ELPC ,
There are some data issues in you example... but here's my take:
IF ToNumber(substring([DateTime_Out],5,2)) >= 7 THEN ToNumber(left([DateTime_Out],4)) +1
ELSE ToNumber(Left(DateTime_Out,4))
ENDIF
Cheers,
Mark
Brilliant! I knew there was a much easier way. Thank you!
@ELPC Not as compact as @MarqueeCrew's but still gets the job done! Attached is the workflow.