Conditional Formulas

AnnieM

Hi, I'm working on a sheet and I'm trying to have a row be highlighted if it is before or after a set date but I can't seem to figure out the formula for that.  My sheet is set up so that I have a column for the start date, a column for the end date, a column for the actual date it was completed, and a column that I will use for the formatting that has flags.  This is the formula that I have come up with so far:

=IF(([Start Date]1 > [Actual Date]1, "0", "1")OR([End Date]1 < [Actual Date]1, "0", "1"))

But after that I'm stuck.  Any help on this would be greatly appreciated!!

 

Mike Wilday

I tried to reinterpret your formula there... But the following one should work if those requirements are correct. Basically, the logic of my formula is:  

IF the Start Date is greater than the Actual Completion Date,

OR the End date is less than the actual date,

THEN set a flag,

ELSE, no flag. 

=IF(OR([Start Date]1 > [Actual Date]1, [End Date]1 <  [Actual Date]1), 1, 0)

Let us know if that works. 

Laura

If I'm understanding your request, You can also use conditional formatting for the highlighting. 

Mike Wilday

Too apply conditional formatting, click on the highlighted button in the screenshot. You will be able to apply highlighting to your rows based on the date.