Excel - Finding Years (1, 2, or 3) by Nesting a DATEDIF() Statement inside an IF() statement

My Objective: In Excel, I need to find out what year (year 1, year 2, year 3, or ENDED) the project has been and will be through fiscal year (FY)2025 (using dates 10/01/24-09/01/25 for FY25).

Version of Excel and Mac -- Using Excel for Mac version 16.96.1 (25042021); 2025 Microsoft. Using MacBook Pro on MacOS: Sequoia 15.4

I have projects A-O that have established start and end dates. However, to keep track of deliverables, I need to keep track of what year the project is in.

For example, Project A has a start and end date of 10/11/21 and 10/10/24.

Year 1 = 10/11/21 - 10/10/22 Year 2 = 10/11/22 - 10/10/23 Year 3 = 10/11/23 - 10/10/24.

Breakdown of the Columns:

Column A: Project Number = Projects A - O

Column B - H = Project Start Date, date of end of the 1st year, start & end dates of the 2nd & 3rd years of the project, as well as the Project End Date (which is the same as the end date for Year 3).

Columns I-T = Dated Oct. 1, 2024 - Sept. 1, 2025. So, for Project A, on Oct. 1, 2024, they were in Year 3. For Columns Nov. 1, 2024 - Sept. 1, 2025, the status = ENDED.

I have used many formulas, but the closest that I have found that works somewhat is the IF(DATEDIF()) nested formula. This gets me close, but in some cells, the year of the project is off by a year. It gives that it's in year 2 and it should be in year 3.

As an example - project F, for Oct-24 (which is Oct. 1, 2024), it should read Year 3, instead the formula is reporting Year 2, which is erroneous.

Looking for a formula that works and not necessarily the IF(DATEDIF()) that I am using.

Here is the formula that I have written in Cell I5 and have dragged down and right.

=IF(DATEDIF($B5,$I$1,"Y")=3,"Year 3",IF(DATEDIF($B5,$I$1,"Y")=2,"Year 2",IF(DATEDIF($B5,$I$1,"Y")=1,"Year 1","ENDED")))

Columns and Rows A5-A19 to T5-T19 is the formula results. However, the desired outcome is A23-A37 to T23-T37.

*Note: The columns show the project year (year 1, 2, 3 or ENDED) as of the date of the columns. Oct-24 column (I22) is actually October 1, 2024 and the status of the project should be shown as of October 1, 2024. Using Project A as an example - On Oct. 1, 2024, the project was still in Year 3, but as of Nov. 1, 2024, the project had ENDED.

For Project G, the start date for year 3 is 11/6/24 so that means that for the Nov-24 column (Nov. 1, 2024), as of Nov. 1, 2024, the project was still in Year 2 and it is in Year 3 on the Dec-24 (or Dec. 1, 2024) column.

Screenshot of my desired outcome:

Since i cannot upload excel sheets - copy the ENTIRE text below and paste it in an excel sheet, pasting in cell A1.

FY25 Start/End Dates    10/1/24 9/30/25                 Date of Interest    4/30/25                                         
Project Year Color  Year 1      Year 2      Year 3                                                      
                                                                            
Project Number  Project Start Date  Year 1 end  Year 2 start    Year 2 end  Year 3 start    Year 3 end  Project End Date    Oct-24  Nov-24  Dec-24  Jan-25  Feb-25  Mar-25  Apr-25  May-25  Jun-25  Jul-25  Aug-25  Sep-25
A   10/11/21    10/10/22    10/11/22    10/10/23    10/11/23    10/10/24    10/10/24    Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3
B   10/11/21    10/10/22    10/11/22    10/10/23    10/11/23    10/10/24    10/10/24    Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3
C   1/3/22  1/2/23  1/3/23  1/2/24  1/3/24  1/2/25  1/2/25  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3
D   1/17/22 1/16/23 1/17/23 1/16/24 1/17/24 10/25/24    10/25/24    Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3
E   1/31/22 1/30/23 1/31/23 1/30/24 1/31/24 1/29/25 1/29/25 Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3
F   7/11/22 7/10/23 7/11/23 7/9/24  7/10/24 7/10/25 7/10/25 Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2
G   11/7/22 11/6/23 11/7/23 11/5/24 11/6/24 3/31/25 3/31/25 Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2
H   2/28/22 2/27/23 2/28/23 2/27/24 2/28/24 12/23/24    12/23/24    Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3
I   10/10/22    10/9/23 10/10/23    10/9/24 10/10/24    10/9/25 10/9/25 Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2
J   3/14/22 3/13/23 3/14/23 3/12/24 3/13/24 3/13/25 3/13/25 Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3
K   6/20/22 6/19/23 6/20/23 6/18/24 6/19/24 5/8/25  5/8/25  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2
L   10/10/22    10/9/23 10/10/23    10/8/24 10/9/24 9/12/25 9/12/25 Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2
M   10/10/22    10/9/23 10/10/23    10/8/24 10/9/24 1/28/25 1/28/25 Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2
N   10/10/22    10/9/23 10/10/23    10/8/24 10/9/24 1/28/25 1/28/25 Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2
O   10/10/22    10/9/23 10/10/23    10/8/24 10/9/24 8/27/25 8/27/25 Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2  Year 2

DESIRED OUTCOME

Project Number  Project Start Date  Year 1 end  Year 2 start    Year 2 end  Year 3 start    Year 3 end  Project End Date    Oct-24  Nov-24  Dec-24  Jan-25  Feb-25  Mar-25  Apr-25  May-25  Jun-25  Jul-25  Aug-25  Sep-25
A   10/11/21    10/10/22    10/11/22    10/10/23    10/11/23    10/10/24    10/10/24    Year 3  ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED
B   10/11/21    10/10/22    10/11/22    10/10/23    10/11/23    10/10/24    10/10/24    Year 3  ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED
C   1/3/22  1/2/23  1/3/23  1/2/24  1/3/24  1/2/25  1/2/25  Year 3  Year 3  Year 3  Year 3  ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED
D   1/17/22 1/16/23 1/17/23 1/16/24 1/17/24 10/25/24    10/25/24    Year 3  ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED
E   1/31/22 1/30/23 1/31/23 1/30/24 1/31/24 1/29/25 1/29/25 Year 3  Year 3  Year 3  Year 3  ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED
F   7/11/22 7/10/23 7/11/23 7/9/24  7/10/24 7/10/25 7/10/25 Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  ENDED   ENDED   ENDED
G   11/7/22 11/6/23 11/7/23 11/5/24 11/6/24 3/31/25 3/31/25 Year 2  Year 2  Year 3  Year 3  Year 3  Year 3  ENDED   ENDED   ENDED   ENDED   ENDED   ENDED
H   2/28/22 2/27/23 2/28/23 2/27/24 2/28/24 12/23/24    12/23/24    Year 3  Year 3  Year 3  ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED
I   10/10/22    10/9/23 10/10/23    10/9/24 10/10/24    10/9/25 10/9/25 Year 2  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3 
J   3/14/22 3/13/23 3/14/23 3/12/24 3/13/24 3/13/25 3/13/25 Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  ENDED   ENDED   ENDED   ENDED   ENDED   ENDED
K   6/20/22 6/19/23 6/20/23 6/18/24 6/19/24 5/8/25  5/8/25  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  ENDED   ENDED   ENDED   ENDED
L   10/10/22    10/9/23 10/10/23    10/8/24 10/9/24 9/12/25 9/12/25 Year 2  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3 
M   10/10/22    10/9/23 10/10/23    10/8/24 10/9/24 1/28/25 1/28/25 Year 2  Year 3  Year 3  Year 3  ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED
N   10/10/22    10/9/23 10/10/23    10/8/24 10/9/24 1/28/25 1/28/25 Year 2  Year 3  Year 3  Year 3  ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED   ENDED
O   10/10/22    10/9/23 10/10/23    10/8/24 10/9/24 8/27/25 8/27/25 Year 2  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  Year 3  ENDED

Let's suppose that you have a sheet like the picture you posted of the desired outcome (but with I23 => T37 being blank). I am also going to suppose that cells I22 => T22 contain 01/10/2024 (October 2024) =>01/09/2025 (September 2025).

You can then go to cell I23 and enter the following code :

=IF(I$22<$B23;"NOT STARTED";IF(I$22<=$C23;"Year 1";IF(AND(I$22>=$D23;I$22<=$E23);"Year 2";IF(AND(I$22>=$F23;I$22<=$G23);"Year 3";IF(I$22>=$H23;"ENDED";"")))))

You can now drag down and right this cell to cover I23 => T37 range (or more if you have more data).