Conditional formatting: if employee is starting, but date is in between two dates, orange

[Mr_Excell_Ensie]

Yes, this morning i had roughly the same question, they couldnt start at the same day.
That one is fixed.

Now i would like to have another check.
If we give an employee a startdate, (and its another project,) and the (startdate in combination with the employee) (already has an entry) (because the startdate is in between two dates) then orange fill.

[vba_php]

there is no question stated here.

[AliGW]

The requirements are stated in the second paragraph.

However, I can’t see where between two dates come into it.

Based on what is stated in post #1, a COUNTIFS on the employee, date and project number columns could be used in a conditional formatting rule.

I am not currently able to open the attachment, sorry.

[GeoffW283]

In the workbook your stated requirement is:
"Employee CANNOT be on DIFFERENT project on SAME date"
That's the rule that I have implemented.

My CF rule highlights all rows that have the same employee and date and have more than one project assigned - is that acceptable?

CF rule applied to A3:E9
=COUNTIFS($C$3:$C$9, $C3, $E$3:$E$9, $E3, $A$3:$A$9, "<>"&$A3) >0

Here's how it looks with your example:

Mr_Excell_Ensie.png

Let us know whether this works for you.

[AliGW]

CF rule:

=SUMPRODUCT((1/(COUNTIF($A$3:A3,$A$3:A3))*($C$3:C3=C3)*($E$3:E3=E3)))>1

Applied to the whole of column E (defined in the rule dialog).

[AliGW]

Your duplicate thread has been closed - please continue here. I've removed the solved tag.

[Mr_Excell_Ensie]

Yesterday had a similar go, but it didnt end up the right way.
(I thank those who have given a go!)

Hence the example given yesterday may be a explained poorly on my behlaf, i tried to give a more comprehensive workbook today.


The logic is; We try and plan employees for a longer period of time.
1 employee cannot start on 2 different projects, with the SAME startdate. (This one is fixed already in my actual workbook and needs NO new formatting.)

In this workbook it should now turn orange or yellow when the following logic is true:

Row 2 has a start and end date, to this period Pete is assigned. (<- This is the logical operator i guess.)
Now when enter the combination of Pete, and a date that is found between another combination of Pete+start/end, it should turn yellow.



Its not impossible for a worker to be on two different projects, on two different days, we just want to be warned to scrutiny if its the most logical to do.

Kind thanks in advance!

[bebo021999]

It require 2 tests: one for upper assigned projectID and another for job assigment in range O:S

CF formula for cel E3:

Please Login or Register  to view this content.

with 1st COUNTIF to test whether Peter is in another job
2nd COUNTIF to test whether Peter has been assigned in Planing

[Mr_Excell_Ensie]

So far it looks exactly what i need!

Can I ask for a very small thing though?

Could please hide the color when there is no entry for employee at all?

All else should do the trick for me, thank you very, very much!

Rep added!

[bebo021999]

So, try to build an AND structure =AND(E2<>"",FORMULA)
CF in E2:

Please Login or Register  to view this content.

[Mr_Excell_Ensie]

Wow Bebo, many thanks again, this works!

[bebo021999]

Attachment with update.

[Mr_Excell_Ensie]

Hi Bebo,
-----edit:
I didnt think i made the example clear enough.
The cell S5, relates to start date in P5, the end date for S5 is in Q5.
So S5 should be blocked in all Column S, for all dates between P5 and Q5.
(S5 works that whole period, so he cant be planned somewhere else.

--Below: Original post


Thank you ever so much

Hopefully you'll find a quick fix to the answer, in the earlier files.
Im actually very confident of that.. :D

Thank you!

Just after the last columns i added the requested outcome.

[bebo021999]

CF formula of S5:

Please Login or Register  to view this content.