Conditional fomating based on date

[anwitha]

Hi,

I need this report to be coloured based on the critera given in sample file.

Note:The cells which are hightlighted in color for each dates in the file are manual, the same needs to be worked in conditional formating based on the date updated in the cell A9.

Requirement is given in the sameple sheet itslef. Pls help.

[shukla.ankur281190]

I'm unable to understand what your actually want to do ??

[kvsrinivasamurthy]

Formulas for CF from 8th row onward.
=D$4<=$A$9
=D$4=$A$9+1
=D$4=$A$9+2
=OR(D$4=$A$9+3,D$4=$A$9+4,D$4=$A$9+7)
=AND(D$4>=$A$9+4,D$4<=$A$9+6)
=AND(D$4>=$A$9+8,D$5<>"S")

[anwitha]

Thanks a lot for sharing the formula...its working well except the below.

1.In ops column it should exclude weekends. Here I need 2 days to be highlighted which is already done by you, but for eg. If 17th is Friday then it should highlight only 17th and 20th (next working day) or if 17th is Saturday then it should highlight color for 20th and 21st.
2.In Result column should highlight highlighted of the 2nd ops day +1 till month end and it should not highlight weekends.
3.in Interview column 3rd highlighted day should be next day of the 2nd ops day (this was not given in my earlier requirement)sorry..for that..

Can this be done? pls help.

[kvsrinivasamurthy]

Is Shifting of days in case of Weekends is required for all like OPS

[anwitha]

no...its required only for OPS and Result.

[kvsrinivasamurthy]

Modifed formulas.Note that n Date Row Numbers are changed to dates and formatted to show only day.
For OPS

Please Login or Register  to view this content.

For Result

Please Login or Register  to view this content.

[anwitha]

Hello, thanks a lot for ur help now its almost 90% done. still have few concern as mentioned below.
1.Iinteveiw & ops date shoul not overlap here its on 17th its overlaping.OPS should start after the 2nd day of interview and after second day of the OPS again one more day has to come for interview. After 1 day interview is completed next day onwards result should start. (but for OPS and Result, Weekend to be excluded how you have done in the formula this needs to be intact.

[kvsrinivasamurthy]

See file

For intervew 2 formulas

=D$4=WORKDAY($A$9+4,2)+1
=OR(D$4=$A$9+3,D$4=$A$9+4)

For Result

=AND(D$4>=WORKDAY($A$9+4,2)+2,WEEKDAY(D$4,2)<6)

[anwitha]

Thank you so much for ur help.

[anwitha]

Hello Murthy sir,
I need one last help...in interview total 3 days are highlighting that is 2 days n 1 is after completing 2nd day of Ops..nw I need total 4 days I.e 3 days n last day is similiar to after completing 2nd day of ops..