Conditional Formatting: Having trouble with time calculations

VRM

New Member
Joined
Jun 27, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,423
Office Version
  1. 365
Platform
  1. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top