2 groups conditional formating

  • Hello ,
    I have two groups of staff, Admin Staff and Teaching Staff.
    I would Like to highlight the Staff Attendance that is not punctual
    The time for Admin Staff should not later than 8.00am where as for the Teaching Staff should not be later than 7.15am.
    How do I use conditional formatting so that I could highlighted the staff that is late as shown in the Example below. Thank you very much for your help.
    Example
    [TABLE="width: 392"]

    [tr]


    [td]

    StaffName

    [/td]


    [td]

    Group

    [/td]


    [td]

    Timein

    [/td]


    [/tr]


    [tr]


    [td]

    Steve

    [/td]


    [td]

    Admin

    [/td]


    [td]

    8.05AM

    [/td]


    [/tr]


    [tr]


    [td]

    Rolland

    [/td]


    [td]

    Teacher

    [/td]


    [td]

    7.00AM

    [/td]


    [/tr]


    [tr]


    [td]

    Rosalind

    [/td]


    [td]

    Teacher

    [/td]


    [td]

    7.16AM

    [/td]


    [/tr]


    [tr]


    [td]

    Jannet

    [/td]


    [td]

    Admin

    [/td]


    [td]

    8.20AM

    [/td]


    [/tr]


    [tr]


    [td]

    Decan

    [/td]


    [td]

    Admin

    [/td]


    [td]

    8.00AM

    [/td]


    [/tr]


    [tr]


    [td]

    Lulu

    [/td]


    [td]

    Teacher

    [/td]


    [td]

    7.18AM

    [/td]


    [/tr]


    [tr]


    [td]

    Hanna

    [/td]


    [td]

    Teacher

    [/td]


    [td]

    6.55AM

    [/td]


    [/tr]


    [tr]


    [td]

    Collin

    [/td]


    [td]

    Teacher

    [/td]


    [td]

    6.50AM

    [/td]


    [/tr]


    [/TABLE]

    [/SIZE][TABLE="width: 262"]

    [tr]


    [td]

    [TABLE="width: 392"]

    [tr]


    [td]

    [TABLE="width: 392"]

    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [td][/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td][/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td][/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td][/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td][/td]


    [td]


    [/td]


    [/tr]


    [/TABLE]

  • Re: 2 groups conditional formating


    Select range...


    Assuming data starts in row 2, go to Home|Conditional Fomatting > New Rule,


    Then "use a formula to determine which cells to format"


    Enter formula:
    [COLOR="#0000FF"]
    =OR(AND($B2="Admin",$C2>TIME(8,0,0)),AND($B2="Teacher",$C2>Time(7,15,0)))[/COLOR]


    Click Format and choose from Font tab

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: 2 groups conditional formating



    Hi Sir,
    I have applied the formula into my worksheet but all the datas were highlighted as below.
    What shoul I do next Sir?


    No Staff Names Posts Date Time In
    [TABLE="width: 633"]

    [tr]


    [td]

    1

    [/td]


    [td]

    BERDAH ANAK RUNIES

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:38:57 AM

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    CHRISTINA CHIA

    [/td]


    [td]

    AKS

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:39:15 AM

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    PAUL YEO

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:40:22 AM

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    AMY NANCY ANAK MICHAEL AE

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:40:49 AM

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    TAROT ANAK AGEH

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:41:23 AM

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    ELZA ANAK LINGKONG

    [/td]


    [td]

    AKS

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:41:40 AM

    [/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    THOMAS SULONG

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:41:56 AM

    [/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td]

    [email protected] ANAK GLAI

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:42:23 AM

    [/td]


    [/tr]


    [tr]


    [td]

    9

    [/td]


    [td]

    RASIT BIN ABAN

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:43:26 AM

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    NIEW ANAK JUPONG

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:44:05 AM

    [/td]


    [/tr]


    [tr]


    [td]

    11

    [/td]


    [td]

    HASZAMAN BIN SIRIEE

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:45:43 AM

    [/td]


    [/tr]


    [tr]


    [td]

    12

    [/td]


    [td]

    MERIAH BINTI JULY

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:46:10 AM

    [/td]


    [/tr]


    [tr]


    [td]

    13

    [/td]


    [td]

    JERRY AK AGIT

    [/td]


    [td]

    AKS

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:46:31 AM

    [/td]


    [/tr]


    [tr]


    [td]

    14

    [/td]


    [td]

    ROLLAND LINGGAT

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:46:51 AM

    [/td]


    [/tr]


    [tr]


    [td]

    15

    [/td]


    [td]

    SUNAINI BIN USIN

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:47:24 AM

    [/td]


    [/tr]


    [tr]


    [td]

    16

    [/td]


    [td]

    NUR JULIANA ABDULLAH

    [/td]


    [td]

    AKS

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:50:22 AM

    [/td]


    [/tr]


    [tr]


    [td]

    17

    [/td]


    [td]

    GLADYS MINDENG

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:51:28 AM

    [/td]


    [/tr]


    [tr]


    [td]

    18

    [/td]


    [td]

    SUUD BIN DENA

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:52:12 AM

    [/td]


    [/tr]


    [tr]


    [td]

    19

    [/td]


    [td]

    JINDU ANAK LUNDU

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:53:15 AM

    [/td]


    [/tr]


    [tr]


    [td]

    20

    [/td]


    [td]

    SINIMA ANAK GIHANG

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:54:45 AM

    [/td]


    [/tr]


    [tr]


    [td]

    21

    [/td]


    [td]

    DONG YAL

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:57:22 AM

    [/td]


    [/tr]


    [tr]


    [td]

    22

    [/td]


    [td]

    PAUL JOSEPH

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:00:14 AM

    [/td]


    [/tr]


    [tr]


    [td]

    23

    [/td]


    [td]

    NANING ANAK AMBA

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:00:26 AM

    [/td]


    [/tr]


    [tr]


    [td]

    24

    [/td]


    [td]

    RASHIDAH BINTI BOL

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:01:34 AM

    [/td]


    [/tr]


    [tr]


    [td]

    25

    [/td]


    [td]

    DIZON ANAK GEMBANG

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:01:56 AM

    [/td]


    [/tr]


    [tr]


    [td]

    26

    [/td]


    [td]

    SITI AZIRA BINTI JOSE

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:02:15 AM

    [/td]


    [/tr]


    [tr]


    [td]

    27

    [/td]


    [td]

    JUNTIA ANAK CLIVE JUSAK

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:02:52 AM

    [/td]


    [/tr]


    [tr]


    [td]

    28

    [/td]


    [td]

    NOR HASIKIN BINTI NONG

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:06:54 AM

    [/td]


    [/tr]


    [tr]


    [td]

    29

    [/td]


    [td]

    KALANA BIN MU

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:07:40 AM

    [/td]


    [/tr]


    [tr]


    [td]

    30

    [/td]


    [td]

    PATRICIA ANAK LAYAS

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:08:09 AM

    [/td]


    [/tr]


    [tr]


    [td]

    31

    [/td]


    [td]

    NORHAIDA BINTI RAMBLI

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:10:24 AM

    [/td]


    [/tr]


    [tr]


    [td]

    32

    [/td]


    [td]

    MUNANG ANAK JAOH

    [/td]


    [td]

    AKS

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:43:40 AM

    [/td]


    [/tr]


    [tr]


    [td]

    33

    [/td]


    [td]

    PIOT EDWARD NAWANG

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    11:01:05 AM

    [/td]


    [/tr]


    [/TABLE]



  • Re: 2 groups conditional formating


    None of those say Teacher or Admin.... Did you adapt the formula to your new rules and references?


    See attached for sample... where only one item (the last row) got highlighted as being extraordinary.

    Files

    • Test.xlsx

      (10.07 kB, downloaded 56 times, last: )

    Where there is a will there are many ways. Finding one that works for you is the challenge!