problems with conditional formatting with array formula

[Anisusa]

hi,

I have a leave planning sheet on that if employee plan their leaves by entering the dates it will highlight the color has green on leave calendar and
holiday will highlight has grey color.

What I need to get results If employee applying their leaves falls on holidays of their leave schedule the holidays color should to change has purple.

Please see the sample attached file and pictures.

[p24leclerc]

instead of returning either 99 for a holiday or 1 or any other number for vacation, try to add those 2 numbers in order to get more information and use it in your conditional formatting.
For example, for a leave falloing on a holiday, you'll end up with a result of 100 that you can use to trigger your purple color.
Regards

[Anisusa]

hi p24leclerc ,

thanks for your reply your example is nice if u dont mind can u make it on sample file.

[Anisusa]

can u pls give me the formula to add both values

i tried to add but i can get it pls help.

[Anisusa]

any body please help....

[p24leclerc]

see attached workbook. I modified January month for you.

[Anisusa]

Hi p24leclerc ,

its perfect working like charm .. thanks a lot for your great help.

you did any array formula modify or only by conditional formatting can u please explain what your trick did on file pls.

[p24leclerc]

First I splitted your formula into 2 parts.
The first part check if it is a holiday and return 99 if so. If not, the value returned is 0.
The second part of the formula check what type of leave it is. So it can return 1, 4 or 5.
If the actual day is not a holiday, the formula return either 0,1,4 or 5 depending if that person is not taking a leave and what type of leave it is.
If the actual day is a holyday, the formula return 99 that is added to the value returned from the leave part (either 0,1,4 or 5).
You end up with the following possiblities: 0,1,4,5,99,100,103 or 104 where
0 means a normal day
1 or 100 (99+1) means type 1 leave (day being or not a holiday)
4 or 103 (99+4) means type 4 leave (day being or not a holiday)
5 or 104 (99+5) means type 5 leave (day being or not a holiday)

Finally, I created a conditional format for each of these values.
Hope this is clear enough for you
Regards

[Anisusa]

wow nice trick really super.. thanks a lot