Using If Statement to assign data to a year

[VBAN0oB]

I am trying to use an IF statement to assign losses to a certain year; however since the policy years differ from client to client it is not like trying to assign based on a fiscal year. So I have my "Effective Date" in cell N6 and in column A I am using the following formula:

=IF(ISBLANK(C2),"",IF(C2<DATE(YEAR($N$6),MONTH($N$6),DAY($N$6)),YEAR(C2)-1&"/"&RIGHT(YEAR(C2),4),YEAR(C2)&"/"&RIGHT(YEAR($N$6)+1,4)))

It appears to be working, until I get to row 16, 17 & 18 (on attached) where my date of loss is late December (the term in column A should be 2023/2024 but instead it is showing 2022/2023). My ask of someone with more brain power than I have is the following:

1. What am I doing wrong in my formula (If my Effective Date in N6 was 12/08/2023 the terms in column A would be correct)
2. How do I make it where column A shows the years as YY/YY instead of YYYY/YYYY - My OCD can't handle it.

 

[AlphaFrog]

1. The date in N6 is year 2024 in your image. The December dates in column C are year 2023. So the formula appears to be correct. If you change the year in N6 to 2023, you should get the correct 2023/2024 for December.

2.

=IF(ISBLANK(C2),"",IF(C2<$N$6,TEXT(EDATE(C2,-12),"YY/")&TEXT(C2,"YY"),TEXT(C2,"YY/")&TEXT(EDATE(C2,12),"YY")))

 

[Peter_SSs]

So the formula appears to be correct. If you change the year in N6 to 2023, you should get the correct 2023/2024 for December.

Changing the N6 date to 2023 does fix those 2023/2024 dates but row 20 then fails as far as I can see.


Assuming that you do not have dates going back to last century, would this also work for you?

=IF(C2="","",LET(yr,RIGHT(YEAR(C2)-(C2<$N$6),2),yr&"/"&yr+1))

If you do have dates going back to cover the 1999/2000 year then

=IF(C2="","",LET(yr,RIGHT(YEAR(C2)-(C2<$N$6),2),yr&"/"&RIGHT(yr+1,2)))

 

[VBAN0oB]

1. The date in N6 is year 2024 in your image. The December dates in column C are year 2023. So the formula appears to be correct. If you change the year in N6 to 2023, you should get the correct 2023/2024 for December.

2.

=IF(ISBLANK(C2),"",IF(C2<$N$6,TEXT(EDATE(C2,-12),"YY/")&TEXT(C2,"YY"),TEXT(C2,"YY/")&TEXT(EDATE(C2,12),"YY")))

N6 is stating that my policy began 12/08/2024; so the loss that occurred 12/28/2023 would have occurred during the 12/08/2023-12/08/2024 term. However, column A row 16, 17 & 17 are showing 22/23 instead of 23/24.

 

[VBAN0oB]

Changing the N6 date to 2023 does fix those 2023/2024 dates but row 20 then fails as far as I can see.


Assuming that you do not have dates going back to last century, would this also work for you?

=IF(C2="","",LET(yr,RIGHT(YEAR(C2)-(C2<$N$6),2),yr&"/"&yr+1))

If you do have dates going back to cover the 1999/2000 year then

=IF(C2="","",LET(yr,RIGHT(YEAR(C2)-(C2<$N$6),2),yr&"/"&RIGHT(yr+1,2)))

Thank you for your help, this got my years to show XX/XX instead of 4 digits and for that I am thankful. However, it is still not showing the right terms for rows 16, 17 & 18. Those three cells should result in 23/24 and it is showing 22/23.

 

[Alex Blakenburg]

This is a fair bit longer but give it a try:

=LET(YrsOffset,IF($N$6>=C2,-DATEDIF(C2,$N$6,"y"),DATEDIF($N$6,C2-1,"y")+1),
EndYear,YEAR($N$6)+YrsOffset,
StartYear,EndYear-1,
txtPeriod,RIGHT(StartYear,2)&"/"&RIGHT(EndYear,2),
txtPeriod)

 

[Peter_SSs]

However, it is still not showing the right terms for rows 16, 17 & 18.

Is this any better?

25 01 25.xlsm
	A		C											N
1
2	21/22		28-Jan-22
3	21/22		11-Apr-22
4	21/22		18-May-22
5	21/22		14-Jun-22
6	21/22		29-Jul-22											08-Dec-24
7	21/22		06-Aug-22
8	21/22		08-Aug-22
9	21/22		30-Sep-22
10	21/22		28-Nov-22
11	21/22		22-Nov-22
12	21/22		06-Jul-22
13	22/23		30-Mar-23
14	22/23		30-Mar-23
15	22/23		24-Jun-23
16	23/24		28-Dec-23
17	23/24		28-Dec-23
18	23/24		28-Dec-23
19	22/23		03-Feb-23
20	23/24		22-Jan-24
21	20/21		02-Mar-21
22	20/21		26-May-21
Year

Cell Formulas
Range	Formula
A2:A22	A2	=IF(C2="","",LET(y,RIGHT(YEAR(C2)-(DATE(YEAR(N$6),MONTH(C2),DAY(C2))<N$6),2),y&"/"&y+1))

 

[Alex Blakenburg]

@VBAN0oB If the Column C date is on an anniversary date, your original formula would put it in the earlier year.
If that was correct just change Peter's formula to be <=N$6

 

[VBAN0oB]

Is this any better?

=IF(C2="","",LET(y,RIGHT(YEAR(C2)-(DATE(YEAR(N$6),MONTH(C2),DAY(C2))<N$6),2),y&"/"&y+1))

Peter, this works perfectly!! Thank you so very much for not giving up on me when I couldn't get it right the first time. I sincerely appreciate your willingness to assist me with this problem.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.