Compare a date between two dates and return correct year

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

Date Time

Common Use Cases

Accepted answers

MarqueeCrew

@ELPC ,

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

DATE/TIME TOOL: MM/dd/yy is used to convert your incoming dates
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

All comments

MarqueeCrew

@ELPC ,

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

DATE/TIME TOOL: MM/dd/yy is used to convert your incoming dates
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.

Date Compare.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703