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

[jamiep004]

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!

[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

[NickSm · Accepted solution]

@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.

[jamiep004]

thank you!!