SOLVED

Conditional Formatting with multiple date conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-2014657%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20with%20multiple%20date%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2014657%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anybody%20can%20help%20me%20with%20the%20formulas%20for%20conditional%20formatting%20or%20anything%20else%20that%20would%20work.%20I%20am%20trying%20to%20format%20as%3A%3C%2FP%3E%3CP%3Eif%20there%20is%20a%20date%20closed%20(%24I)%2C%20the%20row%20to%20remain%20white.%3C%2FP%3E%3CP%3Eif%20there%20is%20no%20date%20closed%20(%24I)%20and%20the%20due%20date(%24H)%20is%20greater%20than%20today(%24k%247)%2C%20the%20row%20to%20turn%20blue%3C%2FP%3E%3CP%3Eif%20there%20is%20no%20date%20closed%20(%24I)%20and%20the%20due%20date%26nbsp%3B(%24H)%20is%20lesser%20than%20today%20(%24k%247)%2C%20the%20row%20to%20turn%20red%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20trying%20to%20use%20some%20formulas%20from%20the%20internet%20search%20but%20doesnot%20seem%20to%20work.%20Any%20help%20would%20be%20appreciated.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2014657%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2014678%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20multiple%20date%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2014678%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F911228%22%20target%3D%22_blank%22%3E%40malihassan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20the%20rows%20that%20you%20want%20to%20format.%3C%2FP%3E%0A%3CP%3EIn%20the%20following%2C%20I%20will%20assume%20that%20the%20active%20cell%20in%20the%20selection%20is%20in%20row%2011.%20%3CEM%3EIf%20it%20is%20in%20another%20row%2C%20adjust%20the%20formulas%20accordingly.%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%20%3DAND(%24I11%3D%22%22%2C%24H11%26gt%3B%24K%247)%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20blue.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERepeat%20the%20above%20steps%2C%20but%20with%20the%20formula%20%3DAND(%24I11%3D%22%22%2C%24H11%26lt%3B%24K%247)%20and%20blue%20as%20fill%20color.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2014682%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20multiple%20date%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2014682%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%20Tried%20doing%20that%20but%20nothing%20happens!%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22malihassan_0-1608853261924.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242770iB03738DA2C7F4980%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22malihassan_0-1608853261924.png%22%20alt%3D%22malihassan_0-1608853261924.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2014941%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20multiple%20date%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2014941%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F911228%22%20target%3D%22_blank%22%3E%40malihassan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20sample%20workbook%20without%20sensitive%20data%3F%20Thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2015147%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20multiple%20date%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2015147%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F911228%22%20target%3D%22_blank%22%3E%40malihassan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFollow%20these%20steps%3A%3C%2FP%3E%3CP%3E1-%20Selcect%20all%20the%20rows%20(%20in%20your%20case%20seems%204%20row)%2C%3C%2FP%3E%3CP%3E2-%20From%20the%20%3CSTRONG%3Ehome%3C%2FSTRONG%3E%20tab%20--%26gt%3B%20on%20the%20%3CSTRONG%3Estyle%3C%2FSTRONG%3E%20group%20--%26gt%3B%3CSTRONG%3EConditional%20formatting%20--%26gt%3B%20New%20rule%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E3-%20%3C%2FSTRONG%3Echoose%3CSTRONG%3E%20Use%20a%20formula%20to%20determine%20which%20cells%20to%20format%20--%26gt%3B%20%3C%2FSTRONG%3Ewrite%20this%20formula%26nbsp%3B%3CSTRONG%3E%3DAND(%24I10%3D%22%22%2C%24H10%26gt%3B%24K%246)%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E4-%26nbsp%3B%20Click%20%3CSTRONG%3Eformat%3C%2FSTRONG%3E%20button%20--%26gt%3B%20choose%20the%20color%20%3CSTRONG%3Eblue%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E5-%20Click%3CSTRONG%3E%26nbsp%3B%20OK%20--%26gt%3B%20OK%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERepeat%20the%20same%20step%20for%20the%20second%20condition%20and%20write%20this%20formula%3A%26nbsp%3B%20%26nbsp%3B%3CSTRONG%3E%3DAND(%24I10%3D%22%22%2C%24H10%26lt%3B%24K%246)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%26nbsp%3B%20ready%20worksheet%20attached%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22formatting1.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242808i91C1469F64F068A2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22formatting1.png%22%20alt%3D%22formatting1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2020703%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20multiple%20date%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2020703%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20been%20out.%20There%20you%20go.%20Been%20trying%20something%20similar%20but%20don't%20get%20the%20expected%20results.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2020855%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20multiple%20date%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2020855%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F911228%22%20target%3D%22_blank%22%3E%40malihassan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20if%20this%20does%20what%20you%20want.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi All, 

 

If anybody can help me with the formulas for conditional formatting or anything else that would work. I am trying to format as:

if there is a date closed ($I), the row to remain white.

if there is no date closed ($I) and the due date($H) is greater than today($k$7), the row to turn blue

if there is no date closed ($I) and the due date ($H) is lesser than today ($k$7), the row to turn red

 

I have been trying to use some formulas from the internet search but doesnot seem to work. Any help would be appreciated. 

 

Thanks! 

7 Replies

@malihassan 

Select the rows that you want to format.

In the following, I will assume that the active cell in the selection is in row 11. If it is in another row, adjust the formulas accordingly.

 

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula =AND($I11="",$H11>$K$7)

Click Format...

Activate the Fill tab.

Select blue.

Click OK, then click OK again.

 

Repeat the above steps, but with the formula =AND($I11="",$H11<$K$7) and blue as fill color.

@Hans Vogelaar 

 

Thanks! Tried doing that but nothing happens!

 

malihassan_0-1608853261924.png

 

@malihassan 

Could you attach a sample workbook without sensitive data? Thanks in advance.

Hi @malihassan 

 

Follow these steps:

1- Selcect all the rows ( in your case seems 4 row),

2- From the home tab --> on the style group -->Conditional formatting --> New rule

3- choose Use a formula to determine which cells to format --> write this formula =AND($I10="",$H10>$K$6) 

4-  Click format button --> choose the color blue

5- Click  OK --> OK

 

Repeat the same step for the second condition and write this formula:   =AND($I10="",$H10<$K$6)

 

Please find  ready worksheet attached

formatting1.png

@Hans Vogelaar 

 

Sorry been out. There you go. Been trying something similar but don't get the expected results. 

 

 

Best Response confirmed by malihassan (New Contributor)
Solution

@malihassan 

See if this does what you want.

@Hans Vogelaar 

 

Thanks! It does! Much appreciated