Conditional Formatting: Having trouble with time calculations

[VRM]

Hi Folks,
Excel novice.
I have always had a problem with time calculations.
I know that nested conditional statements are required to meet my desired goal.
Your patience is appreciated.

The conditions

• Values Required within 30 days of Start Date
• Values Required within 30 days of Midpoint
• Values Required within 60 days of End Date

Time variables - these calculations are shaky

• Date of Report = Introduction!C$2 (fixed cell in the workbook)
• 30 Days from Start Date: [@[Start Date]]+30
• Midpoint: (([@[End Date]]+[@[Start Date]])/2)
• 60 Days until End Date: [@[End Date]]-60

Separate Conditional Statements -- need to be verified and combined into one statement

Startup (30 Days from Start Date)
=IF(AND([@[Start Date]]+30)<= Introduction!C$2,[@[Proposal Document]]="Yes",[@[Tag Status]]="Complete",[@Overview]="Yes",[@Activities]="Yes"),"4 OK","2 Startup Red")

Midpoint
=IF(AND((([@[End Date]]+[@[Start Date]])/2)<= Introduction!C$2,[@[Proposal Document]]="Yes",[@[Tag Status]]="Complete",[@Overview]="Yes",[@Activities]="Yes",[@[Project Deliverables]]="Yes"),"4 OK","3 Yellow")

Closeout (60 Days until End Date)
=IF(AND(([@[End Date]]-60)<=Introduction!C$2,[@[KHub Project Completeness]]="Complete"), "Green","1 Closeout Red")

Again, your help is appreciated.

 

[etaf]

What does not work,
you have a lot of AND()

Midpoint , is that looking for a date
as AND((([@[End Date]]+[@[Start Date]])/2) will be a number of days divided by 2

but Introduction!C$2
seems to be a date

• Date of Report = Introduction!C$2 (fixed cell in the workbook).
• 30 Days from Start Date: [@[Start Date]]+30 = if start date was 1st Jan then adding 30 would now be 30th Jan
• Midpoint: (([@[End Date]]+[@[Start Date]])/2) = this is just a number - lets say End date = 20th jan and start date 10th then diff = 10 divid 2 = 5
• 60 Days until End Date: [@[End Date]]-60 = if start date was 1st Jan then - 60 days is the 2nd nov previous year

I dont like the @ terms personally and change those cells into actual column row - like B2 etc to test whats going on
ALSO
USE just the AND and see if you get a TRUE or FALSE - make sure the result is what you expert

otherwise a sheet loaded here in XL2BB see signature