SOLVED

Conditional formatting help needed

%3CLINGO-SUB%20id%3D%22lingo-sub-2335011%22%20slang%3D%22en-US%22%3EConditional%20formatting%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335011%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20set%20up%20a%20rule%20for%20conditional%20formatting%20but%20kind%20of%20stuck.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20sheet%20with%20a%20list%20of%20invoices.%20I%20want%20to%20highlight%20rows%20that%20are%20past%20due%20date%20and%20haven't%20been%20paid%20yet.%20I%20have%20two%20columns%20that%20are%20used%20in%20this%20case.%20Column%20D%20(starting%20at%20D%3A2)%20is%20the%20%22due%20date%22%20column%2C%20and%20column%20E%20(starting%20at%20E%3A2)%20is%20the%20one%20where%20I%20type%20in%20the%20dates%20when%20I've%20made%20the%20payments.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20essentially%20I%20would%20like%20to%20set%20up%20a%20rule%20that%20tests%20column%20D%20for%20the%20date%20being%20older%20than%20today%20and%20column%20D%20if%20there%20is%20anything%20in%20that%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20assist%20me%20on%20this%3F%20All%20help%20would%20be%20much%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20great%20day!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreg%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2335011%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-2335119%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335119%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047820%22%20target%3D%22_blank%22%3E%40gergelycsaszar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Greg%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20Use%20the%20formula%20as%20shown%20below.%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%22MEFC%20Frormula.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279073iB84C58DDE5C06596%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22MEFC%20Frormula.png%22%20alt%3D%22MEFC%20Frormula.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20that%20dates%20are%20franche%20formated%20in%20this%20picture%2C%20and%20that%20references%20to%20E2%20and%20D2%20are%20relative%20so%20that%20the%20formula%20can%20work%20for%20every%20selected%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2335124%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335124%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047820%22%20target%3D%22_blank%22%3E%40gergelycsaszar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20apply%20the%20rule%20to%20your%20entire%20range%20(e.g.%20D2%3AE1000)%20with%20rue%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D(%24D2%3CTODAY%3E%3C%2FTODAY%3E%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2335177%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335177%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047665%22%20target%3D%22_blank%22%3E%40ericGuyaderBerger%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20for%20the%20quick%20reply%2C%20however%20I%20couldn't%20manage%20to%20apply%20the%20rule%20properly.%20I%20have%20attached%20a%20slimmed%20down%20version%20of%20the%20XLSX%2C%20can%20you%20please%20have%20a%20look%20and%20let%20me%20know%20what%20I%20missed%3F%20I%20would%20like%20to%20highlight%20the%20entire%20row%20where%20the%20date%20is%20before%20today%20AND%20there%20is%20nothing%20in%20the%20%22PAID%22%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20a%20lot%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2335178%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335178%22%20slang%3D%22en-US%22%3Ethank%20you%20very%20much.%20I%20have%20tried%20but%20it%20didnt't%20work.%20can%20you%20please%20have%20a%20look%20at%20my%20post%20above%20with%20the%20excel%20sheet%20attached%3F%20thanks%20a%20lot!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2335196%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335196%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047820%22%20target%3D%22_blank%22%3E%40gergelycsaszar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20your%20file%20with%20the%20conditional%20format%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bformula%20would%20also%20do%20the%20trick.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20the%20one%20you%20prefer!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2335198%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335198%22%20slang%3D%22en-US%22%3Eawesome%2C%20thank%20you%20very%20much.%20is%20there%20a%20way%20to%20highlight%20the%20whole%20row%20not%20just%20the%20cell%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2335199%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335199%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047820%22%20target%3D%22_blank%22%3E%40gergelycsaszar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi all,

 

I'm trying to set up a rule for conditional formatting but kind of stuck.

 

I have a sheet with a list of invoices. I want to highlight rows that are past due date and haven't been paid yet. I have two columns that are used in this case. Column D (starting at D:2) is the "due date" column, and column E (starting at E:2) is the one where I type in the dates when I've made the payments. 

 

So essentially I would like to set up a rule that tests column D for the date being older than today and column D if there is anything in that cell.

 

Can you assist me on this? All help would be much appreciated.

 

Have a great day!

 

Greg

10 Replies

@gergelycsaszar 

 

Hi Greg,

 

You could Use the formula as shown below.

 

MEFC Frormula.png

 

Note that dates are franche formated in this picture, and that references to E2 and D2 are relative so that the formula can work for every selected cell.

 

Hope this helps!

 

@gergelycsaszar 

You may apply the rule to your entire range (e.g. D2:E1000) with rue formula

=($D2<TODAY())*($E2="")

@ericGuyaderBerger 

 

thank you for the quick reply, however I couldn't manage to apply the rule properly. I have attached a slimmed down version of the XLSX, can you please have a look and let me know what I missed? I would like to highlight the entire row where the date is before today AND there is nothing in the "PAID" column.

 

thanks a lot

thank you very much. I have tried but it didnt't work. can you please have a look at my post above with the excel sheet attached? thanks a lot!

@gergelycsaszar 

 

Here is your file with the conditional format formula.

 

@Sergei Baklan formula would also do the trick.

 

Use the one you prefer!

awesome, thank you very much. is there a way to highlight the whole row not just the cell?
best response confirmed by gergelycsaszar (Occasional Contributor)
Solution

@gergelycsaszar 

Please check in attached file.

@gergelycsaszar 

Full row is highlighted, please check the file.

 

As a comment, I'd convert your range into structured table (Ctrl+T), when rules will be expanded automatically.

thank you very much. it all works now

@gergelycsaszar , you are welcome