Basic Conditional Formatting

David0079

New Member
Joined
Jul 26, 2007
Messages
44
Mornin all,

I have two columns of data, E & F. E contains dates and F contains status, i.e. Started, In Progress, Finished. What kind of formula would I use in conditional format to get the status box to change to red if the date in E had passed but F said In Progress?
Is it something like this....

=AND(E1:E5<TODAY(),F1:F5="In Progress") Am I being a bit simple? :biggrin:
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

JV0710

Active Member
Joined
Oct 26, 2006
Messages
398
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Conditional Formatting

Hi David

Welcome to the board

For your query

Select cell E1, and open conditional formatting window.
Under Condition, you select "formula is" instead of "cell value is"
Then type this formula:
=IF(AND((E1>0),(F1="In progress")),IF(E1<TODAY(),1,0))
and format to RED

Then copy this format to all cells in column F that need this conditional format

Hope this helps

JVN
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,321
Office Version
  1. 365
Platform
  1. Windows
David0079

Welcome to the MrExcel board!

I'm going basically with DiscoPistol's solution but point out that TODAY() is a "volatile" function and can slow your sheet down considerably if you use the function a lot of time on the sheet. Therefore, if you have quite few rows of data, I suggest you put =TODAY() in an empty cell somewhere and just refer to that cell in your conditional formatting formula. That way, TODAY() only has to be calculated once each time, not once for every cell that contains it in the CF formula. Like this:

Excel Workbook
EFGH
1DateStatus01/08/2007
227/07/2007In Progress
328/07/2007Started
429/07/2007Finished
530/07/2007In Progress
631/07/2007In Progress
701/08/2007Finished
802/08/2007Started
903/08/2007In Progress
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F11. / Formula is =AND(E1<$H$1,F1="In Progress")Abc
F21. / Formula is =AND(E2<$H$1,F2="In Progress")Abc
F31. / Formula is =AND(E3<$H$1,F3="In Progress")Abc
F41. / Formula is =AND(E4<$H$1,F4="In Progress")Abc
F51. / Formula is =AND(E5<$H$1,F5="In Progress")Abc
F61. / Formula is =AND(E6<$H$1,F6="In Progress")Abc
F71. / Formula is =AND(E7<$H$1,F7="In Progress")Abc
F81. / Formula is =AND(E8<$H$1,F8="In Progress")Abc
F91. / Formula is =AND(E9<$H$1,F9="In Progress")Abc
 

DiscoPistol

Active Member
Joined
Jun 6, 2006
Messages
261
I did wonder what Volatile meant....... :eek:

You learn something everyday.

Thanks Peter
 

JV0710

Active Member
Joined
Oct 26, 2006
Messages
398
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Good day

My Apoogies, I got bumped off the network

The formula in my post should have read:
=IF(E1>0,IF(AND(E1<TODAY(),F1="In Progress"),1,0))

Peter: Your tip on using another cell to calculate "Today()" is great . . . I never thought of that
 

JV0710

Active Member
Joined
Oct 26, 2006
Messages
398
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Okay . . . it's still not posting the complete line . . . Sorry guys
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,321
Office Version
  1. 365
Platform
  1. Windows
Okay . . . it's still not posting the complete line . . . Sorry guys
That often happens when you use "<" or ">" symbols in your post and it is interpreted as HTML code. The solution is to enclose your formula between code tags like this:
{code}your formula{/code}
... only use [square brackets] instead of the {curly ones}.
 

Forum statistics

Threads
1,181,404
Messages
5,929,750
Members
436,687
Latest member
Glass of Gin

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