Conditional formatting to show when job is done

Report
Posts
1
Registration date
Wednesday November 6, 2019
Status
Member
Last seen
November 6, 2019
-
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
-
HI,
I have work tracking file. all is running good only i want to have conditional formatting in it.

for example:- in sheet dme, column f12:f50 is for planned work. all the dates in f12:f50 are sign of planned work column g12:g50 is for actual work dates(when the work is getting done, actually)

my requirement is that if f12 is not blank and g12 is blank for today then g12= yellow colour

and if f12>= g12 then g12= green colour.

if f12<g12 then g12= red colour

if f12 is not blank and g12 is blank and planned date has gone then also g12=red colour( if f12 = 4 nov2019 and g12 is blank and today date is 5-11-2019 then it should be automatically red even if blank.) and so on for all the cells.
please see sheet desire result. looking after for the answer.https://accounts.google.com/ServiceLogin?service=wise&passive=1209600&continue=https://docs.google.com/spreadsheets/&followup=https://docs.google.com/spreadsheets/<mpl=sheets#gid=177123027

1 reply

Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
440
Hi Sanjeev,

Here is the conditional formatting window:


1) =OR($F12<$G12,AND($F12<>"",$G12="",$F12<TODAY()))
2) =AND($F12<>"",$G12="",$F12>TODAY())
3) =$F12>=$G12

The order of the rules is important. Also check the applied range.

Best regards,
Trowa