Label date ranges so that if "date" falls within a given range that range is returned

Hi Community !

09-01-2013 - 08-31-2014 = FY14

09-01-2014 - 08-31-2015 = FY15

09-01-2015 - 08-31-2016 = FY16

09-01-2016 - 08-31-2017 = FY17

09-01-2017 - 08-31-2018 = FY18

I want to say if the date in the date column falls within any of these ranges return the corresponding FY. How can I do this?

Thanks!

Workflow

Date Time

Accepted answers

NickSm

@jamiep004

Sure, so a couple things here actually just to make it work in the formula tool. The dates are treated as strings, so you'll want quotes around them. And instead of

Date1 < [Date] < Date2

it will be in the format of

Date1 < [Date] AND [Date] < Date2

Like so:

IF "2015-07-01"<[Invoice Date] AND [Invoice Date]<"2016-06-30" THEN "FY16"
ELSEIF "2016-07-01"<[Invoice Date] AND [Invoice Date] <"2017-06-30" THEN "FY17"
ELSEIF "2017-07-01"<[Invoice Date] AND [Invoice Date] < "2018-06-30" THEN "FY18"
ELSEIF "2018-07-01"<[Invoice Date] AND [Invoice Date]<"2019-06-30" THEN "FY19"
ELSE "FY20"
ENDIF

Attaching a working example.

FY_Append.yxmd

All comments

NickSm

Hey @jamiep004 ,

Step one will just be to put your dates into the recognized date-time format of yyyy-mm-dd

Then within a formula tool you can create a new column with an If statement like:

If 2013-09-01 < [Date] < 2014-08-31 then "FY14"

Elseif 2014-09-01 < [Date] < 2015-08-31 then "FY15"

Elseif .....

Else [FY18]

Endif

jamiep004

Thank you for your quick response!

But can you tell me why I would get an "Invalid type in operator <." error with this formula?

IF 2015-07-01<[Invoice Date]<2016-06-30
THEN "FY16"
ELSEIF 2016-07-01<[Invoice Date]<2017-06-30
THEN "FY17"
ELSEIF 2017-07-01<[Invoice Date]<2018-06-30
THEN "FY18"
ELSEIF 2018-07-01<[Invoice Date]<2019-06-30
THEN "FY19"
ELSE "FY20"
ENDIF

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703