excel date if-statement conditional-formatting

Excel - Nested IF(AND()) Statements to Report Multiple Parameters

My Objective:

In Excel, I need to identify 'Special' projects. The excel formula already identifies if a project has 'Not Started' and 'Ended,' as well as what year (year 1, year 2, year 3) the project is in. To do so I am using dates through fiscal years (FY). For example, for FY 2025 - dates are 10/01/24-09/01/25 and;
Copy and paste the color of the conditional formatting to a different cell, when needed.

Here is a link to an excel sheet: https://docs.google.com/spreadsheets/d/14mgyWs1nu2FEsAY4ov-uDdmWGwMIsRFy/edit?usp=drive_link&ouid=101239920619390949977&rtpof=true&sd=true

The desired outcome is on rows 1-17 and what the formulas are generating are on rows 23-39.

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 (row 2 [desired outcome] or row 24 [formula outcome]) = Dated Oct. 1, 2024 - Sept. 1, 2025.

What I am looking to do is essentially add to the IF() formula that starts on cell I25 in that if the "Project Type" (column U) states 'Special' for it to report 'Special' on the condition that if the project has ended (based on the dates provided on Columns I24-T24, that it reports "ENDED" instead. All other projects that do not have 'Special' under "Project Type," the formula works as expected.

I have tried many iterations of IF(AND()) and IF(OR()) statements to do this but it's not giving me quite what I need. I have this formula that puts all desired outcomes (e.g., Not started, Year 1, Year 2, Year 3, and Ended) but it does not place the 'Special' for 'Special projects.

=IF(I$24<$B25,"NOT STARTED",IF(I$24<=$C25,"Year 1",IF(AND(I$24>=$D25,I$24<=$E25),"Year 2",IF(AND(I$24>=$F25,I$24<=$G25),"Year 3", IF(OR(I$24>= $H25,$U25="Special"), "ENDED", IF($U25="Special", "Special", ""))))))

Part II of objective: The second objective is to be able to copy the color of the conditional formatting to a different cell. I have tried to copy, then paste special then conditional formatting only in the hopes to just paste the color of the conditional formatting, but this approach is not working. Is there a way to paste to different cells the color of the cells, based on if the cells state "Not Started," "Year 1," "Year 2," "Year 3," "Ended," or now "Special?"

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

Any help is greatly appreciated. Thank-you.

Solution

If I understand correctly, you want to show Special instead of Year n if the word Special exists in column U.

That being the case, try:

=LET(
    a, XLOOKUP(
        I$24,
        $C25:$H25,
        REGEXEXTRACT($C$24:$H$24, "^.*\d+"),
        "ENDED",
        1
    ),
    b, IF(
        $B25 > I$24,
        "Not Started",
        IF(a = "ENDED", "ENDED", IF($U25 = "Special", "Special", a))
    ),
    b
)

Adjusting the range references to reflect your data location.
The header row in my data sample was in Row 24, with Project Start Date in Column B

So far as your second request, conditional formatting cannot be copy/pasted using a formula. You will need to do that either manually, or by writing a VBA solution.

If you do not want to do it manually, and cannot figure out how to script it, I suggest you ask a separate question. Although the macro recorder might be a good start to figure out how to script it. I'm not sure how that works in Excel for MAC.