Results 1 to 7 of 7

Thread: Find the Clash of duty time in excel

  1. #1
    Neophyte vinoo's Avatar
    Join Date
    Jun 2019
    Posts
    3
    Articles
    0
    Excel Version
    2007

    Find the Clash of duty time in excel



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    If i want to create a duty schedule for my employees and i want to check the clash of duty for a person.For example
    John has a schedule for Monday 9.30 to 10.30 in Department A.... if i enter the same name for Department B on Monday 9.30 to 10.30
    excel should show an error and highlight cells for clash of duty as he cannot do the duty at the same time in two different departments.
    Please help how to highlight if there is a clash

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,239
    Articles
    0
    Excel Version
    Office 365 Subscription
    Please attach your workbook.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Neophyte vinoo's Avatar
    Join Date
    Jun 2019
    Posts
    3
    Articles
    0
    Excel Version
    2007
    Hi,

    If i want to create a duty schedule for my employees and i want to check the clash of duty for a person.For example
    John has a schedule for Monday 9.30 to 10.30 in Department A.... if i enter the same name for Department B on Monday 9.30 to 10.30
    excel should show an error and highlight cells for clash of duty as he cannot do the duty at the same time in two different departments.
    In the sheet i have used a color to highlight but how to do that with the help of a formula
    Please help how to highlight if there is a clash

    Attached Files Attached Files

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,239
    Articles
    0
    Excel Version
    Office 365 Subscription
    This won't work. You need to have a column for start time and another for finish time populated with proper times, not text. Then you can put together an easy formula.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Neophyte vinoo's Avatar
    Join Date
    Jun 2019
    Posts
    3
    Articles
    0
    Excel Version
    2007
    Hope this sheet is okay..Please find the attached file
    Attached Files Attached Files

  6. #6
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by vinoo View Post
    he cannot do the duty at the same time in two different departments.
    If it can help you, try one of these formulas
    Code:
    =AND(IF(COUNTIF($F$2:$F2;$A2&$C2)>1;TRUE;FALSE)=TRUE;IF(COUNTIF($G$2:$G2;$A2&$B2)>1;TRUE;FALSE)=FALSE)
    =AND(COUNTIFS($A$2:$A$7;$A2;$C$2:$C$7;$C2)+COUNTIFS($A$2:$A$7;$C2;$C$2:$C$7;$A2)>1;COUNTIFS($B$2:$B$7;$B2;$C$2:$C$7;$C2)+COUNTIFS($B$2:$B$7;$C2;$C$2:$C$7;$B2)>1)
    =AND(COUNTIFS($A$2:$A$7;$A2;$C$2:$C$7;$C2)+COUNTIFS($A$2:$A$7;$C2;$C$2:$C$7;$A2)>1;COUNTIFS($B$2:$B$7;$B2;$C$2:$C$7;$C2)+COUNTIFS($B$2:$B$7;$C2;$C$2:$C$7;$B2))
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  7. #7
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    21
    Articles
    0
    Excel Version
    2016
    FYI
    1. select B7:F30
    2.Select "conditional formating"->new rule->Use formula...., then paste following formula to "Format values"

    =AND(B7<>"",COUNTIFS($B$7:$B$30,B7,$C$7:$C$30,C7,$D$7:$D$30,D7,$E$7:$E$30,E7,$F$7:$F$30,F7)>1)
    3. choose your color for highlight, "ok" to exit


    Quote Originally Posted by vinoo View Post
    Hi,

    If i want to create a duty schedule for my employees and i want to check the clash of duty for a person.For example
    John has a schedule for Monday 9.30 to 10.30 in Department A.... if i enter the same name for Department B on Monday 9.30 to 10.30
    excel should show an error and highlight cells for clash of duty as he cannot do the duty at the same time in two different departments.
    In the sheet i have used a color to highlight but how to do that with the help of a formula
    Please help how to highlight if there is a clash


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •