Adding FY based on a date

Chris Hallo

Hello! So, our FY runs from 7/1 through 6/30 and based on Loss Date, I want it to add the FY for FY14 through 20 as that is the data set. I used this formula though currently it is #UNPARSABLE. What am I doing wrong?

=IF(AND([“Loss Date”] DATE(2013, 7, 1), [“Loss Date”] <= DATE(2014, 6, 30)), "FY14", IF(AND([“Loss Date”] DATE(2014, 7, 1), [“Loss Date”] <= DATE(2015, 6, 30)), "FY15", "") IF(AND([“Loss Date”] DATE(2015, 7, 1), [“Loss Date”] <= DATE(2016, 6, 30)), "FY16", "") IF(AND([“Loss Date”] DATE(2016, 7, 1), [“Loss Date”] <= DATE(2017, 6, 30)), "FY17", "") IF(AND([“Loss Date”] DATE(2017, 7, 1), [“Loss Date”] <= DATE(2018, 6, 30)), "FY18", "") IF(AND([“Loss Date”] DATE(2018, 7, 1), [“Loss Date”] <= DATE(2019, 6, 30)), "FY19", "") IF(AND([“Loss Date”] DATE(2019, 7, 1), [“Loss Date”] <= DATE(2020, 6, 30)), "FY20", "") IF(AND([“Loss Date”] DATE(2020, 7, 1), [“Loss Date”] <= DATE(2021, 6, 30)), "FY21", "")

I would appreciate any assistance.

Thank you ☺️

L_123

Your and Statement:

AND([“Loss Date”] DATE(2014, 7, 1), [“Loss Date”] <= DATE(2015, 6, 30))

You do not signify the relationship between Loss Date and the date.

Unless you named your column "Loss Date" instead of Loss Date, you don't need the quotes.

You don't specify the row to look for on the loss date column. If if is the same row, you can use an @row reference, otherwise you need to specify the row number

[Loss Date]@row or [Loss Date]50 instead of just [Loss Date]

Those are what are preventing your formula from working, but this is inefficient as well. If you fix the above the formula should do what you want, but it can be made much shorter and less resource intensive, as you are only making use of the true side of the if statement.

example

=if([question one]@row = "yes","true", if([question one]@row = "no","false"))

If you know that the value in the question one column can only be yes or no you can do something like this:

=if([question one]@row = "yes","true","false")

You can use this in dates as well. Example

=if(today()>date@row, "the date is in the past", "the date is either today or in the future")

You could sequence all of this out and have an if statement about half the length of yours, but you would need to remember to edit it annually to fix the formula as you move forward and add to the sheet, and there is still a more efficient way to do this.

Give this a try:

="FY" + (VALUE(RIGHT(YEAR([Loss Date]@row), 2)) - IF(VALUE(MONTH([Loss Date]@row)) > 6, 1, 0))