Home

Conditional Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-544689%22%20slang%3D%22en-US%22%3EConditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-544689%22%20slang%3D%22en-US%22%3EHi%20all%3CBR%20%2F%3E%3CBR%20%2F%3EI%E2%80%99m%20trying%20to%20get%20cell%20D%20in%20the%20attached%20photo%20to%20change%20either%20red%20if%20cell%20C%20is%20before%20today%E2%80%99s%20date%2C%20Green%20if%20cell%20C%20is%20after%20today%E2%80%99s%20date%20and%20Clear%20if%20no%20data%20is%20in%20Cell%20C%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20do%20I%20also%20get%20this%20to%20happen%20to%20approx%20150%20cells%3F%20With%20out%20it%20populating%20all%20the%20way%20down%20cell%20D%20as%20in%20the%20other%20Photo%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20in%20Advance%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-544689%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-545277%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-545277%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F337731%22%20target%3D%22_blank%22%3E%40Nevpeek%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhoa%2C%20you%20may%20want%20to%20learn%20how%20to%20use%20the%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-nz%2Fhelp%2F13776%2Fwindows-use-snipping-tool-to-capture-screenshots%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ESnipping%20tool%3C%2FA%3E%20that%20is%20built%20into%20your%20computer%2C%20so%20you%20can%20take%20screenshots%20with%20software%20instead%20of%20using%20a%20camera%20to%20photograph%20what's%20on%20your%20monitor.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20also%20attach%20a%20sample%20file%20to%20a%20post%20here.%20Use%20the%20%22Choose%20Files%22%20button%20below%20the%20reply%20box%20to%20attach%20a%20file%20with%20your%20post.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20to%20your%20question%3A%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20seems%20that%20you%20have%20two%20questions.%20One%20is%20about%20conditional%20formatting%2C%20and%20the%20other%20one%20is%20about%26nbsp%3B%20how%20to%20show%20the%20date%20only%20in%20rows%20that%20have%20a%20real%20date%20in%20column%20C.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20to%20show%26nbsp%3B%3CSTRONG%3Enext%20refresher%20due%3C%2FSTRONG%3E%20only%20for%20rows%20with%20a%20date%20in%20column%20C%3A%20Start%20in%20D4%20with%20this%20formula%20and%20copy%20down.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(C4%26gt%3B0%2CDATE(YEAR(C4)%2B1%2CMONTH(C4)%2CDAY(C4))%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20to%20use%20conditional%20formatting%20to%20%3CSTRONG%3Ehighlight%3C%2FSTRONG%3E%20cells%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20cell%20D4%20down%20to%20the%20bottom%20of%20your%20list.%20Then%20click%20Conditional%20formatting%20%26gt%3B%20New%20Rule%20%26gt%3B%20Use%20a%20formula%20to%20determine%20...%20%26gt%3B%20and%20enter%20this%20formula%20into%20the%20formula%20box%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DAND(ISNUMBER(%24C4)%2C%24C4%26gt%3BTODAY())%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Euse%20green%20for%20that.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20add%20another%20rule%20with%20this%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DAND(ISNUMBER(%24C4)%2C%24C4%3CTODAY%3E%0A%3C%2FTODAY%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%20red%20for%20that.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20not%20sure%20if%20that%20is%20really%20what%20you%20want%20to%20show%2C%20but%20this%20does%20what%20you%20described.%20If%20the%20date%20in%20C%20is%20less%20than%20today%2C%20D%20will%20be%20red.%20If%20the%20date%20in%20C%20is%20greater%20than%20today%2C%20D%20will%20be%20green.%20If%20there%20is%20no%20date%20in%20C%2C%20D%20will%20not%20change%20colour.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20does%20not%20really%20make%20sense%20that%20the%20last%20refresher%20training%20in%20C%20would%20be%20a%20date%20that%20is%20greater%20than%20today%2C%20but%20that%20is%20what%20you%20asked%20for.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20screenshot%20shows%20the%20formula%20for%20column%20D%20in%20the%20formula%20bar%20and%20the%20rule%20for%20the%20green%20conditional%20format%20following%20your%20requirements%20(which%20don't%20make%20sense)%2C%20because%20the%20last%20refresher%20training%20cannot%20be%20in%20the%20future.%20Or%20can%20it%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112542i71887D109FC9C6BA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-05-08_18-31-34.png%22%20title%3D%222019-05-08_18-31-34.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EI'm%20attaching%20the%20file%20so%20you%20can%20have%20a%20play%20with%20it%20.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-545374%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-545374%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%2C%20its%20done%20the%20trick.%3CBR%20%2F%3E%3CBR%20%2F%3ESorry%20about%20the%20picture%20I%20do%20know%20about%20snipping%20tool%20I%20just%20couldn't%20use%20it%20to%20be%20able%20to%20do%20this%2C%20as%20I%20couldn't%20get%20to%20this%20site%20on%20my%20work%20computer%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20purpose%20of%20it%20is%20to%20show%20when%20we%20will%20need%20to%20contact%20people%20to%20do%20training%3CBR%20%2F%3E%3CBR%20%2F%3ELast%20refresher%20training%20wont%20be%20the%20future%2C%20that%20will%20be%20the%20past%20and%20next%20refresher%20will%20be%20a%20future%20date%20dependent%20on%20when%20the%20last%20training%20was%20conducted%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-545464%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-545464%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3BSorry%20looks%20like%20something%20funny%20is%20going%20on.%3C%2FP%3E%3CP%3Eanything%20before%20the%20current%20day%20needs%20to%20be%20Red%20and%20anything%20that%20is%20in%20the%20future%20needs%20to%20be%20Green%20in%20the%20Next%20refresher%20training%20cell%3C%2FP%3E%3CP%3EAlso%20if%20you%20were%20to%20input%20the%20current%20day%20into%20the%20last%20refresher%20training%20it%20fills%20in%20the%20next%20refresher%20with%20a%20clear%20box%2C%20how%20could%20I%20go%20about%20having%20that%20as%20green%20aswell%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Nevpeek
New Contributor
Hi all

I’m trying to get cell D in the attached photo to change either red if cell C is before today’s date, Green if cell C is after today’s date and Clear if no data is in Cell C

How do I also get this to happen to approx 150 cells? With out it populating all the way down cell D as in the other Photo

Thanks in Advance
3 Replies

@Nevpeek 

Whoa, you may want to learn how to use the Snipping tool that is built into your computer, so you can take screenshots with software instead of using a camera to photograph what's on your monitor. 

 

You can also attach a sample file to a post here. Use the "Choose Files" button below the reply box to attach a file with your post. 

 

Now to your question: 

 

It seems that you have two questions. One is about conditional formatting, and the other one is about  how to show the date only in rows that have a real date in column C.

 

How to show next refresher due only for rows with a date in column C: Start in D4 with this formula and copy down.

 

=IF(C4>0,DATE(YEAR(C4)+1,MONTH(C4),DAY(C4)),"")

 

How to use conditional formatting to highlight cells:

 

Select cell D4 down to the bottom of your list. Then click Conditional formatting > New Rule > Use a formula to determine ... > and enter this formula into the formula box:

 

=AND(ISNUMBER($C4),$C4>TODAY())

 

use green for that.

 

Then add another rule with this formula:

 

=AND(ISNUMBER($C4),$C4<TODAY())

 

Use red for that.

 

I'm not sure if that is really what you want to show, but this does what you described. If the date in C is less than today, D will be red. If the date in C is greater than today, D will be green. If there is no date in C, D will not change colour. 

 

It does not really make sense that the last refresher training in C would be a date that is greater than today, but that is what you asked for. 

 

The screenshot shows the formula for column D in the formula bar and the rule for the green conditional format following your requirements (which don't make sense), because the last refresher training cannot be in the future. Or can it?

2019-05-08_18-31-34.png

I'm attaching the file so you can have a play with it .

 

 

 

 

Thank you so much, its done the trick.

Sorry about the picture I do know about snipping tool I just couldn't use it to be able to do this, as I couldn't get to this site on my work computer

The purpose of it is to show when we will need to contact people to do training

Last refresher training wont be the future, that will be the past and next refresher will be a future date dependent on when the last training was conducted

@Ingeborg Hawighorst Sorry looks like something funny is going on.

anything before the current day needs to be Red and anything that is in the future needs to be Green in the Next refresher training cell

Also if you were to input the current day into the last refresher training it fills in the next refresher with a clear box, how could I go about having that as green aswell?

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies