Results 1 to 10 of 10

Thread: Highlight Overlap dates

  1. #1
    Junior Member
    Join Date
    Mar 2017
    Posts
    14
    Rep Power
    0

    Post Highlight Overlap dates

    Hi,

    My previous post may not be explaining clearly to understand the problems. Let me try to give some more clear solution to understand what results I need in my file.

    I have a Four Employees. Each employee they want to prepare a leave plan for year 2017. If employees, they want to apply leave Rule is there should not be any overlap date with other employee’s dates.

    Now I wish to compare the employees overlap dates by selecting the checkbox. In case any overlapping dates between the employees then I need to highlight the overlap, Column has red color in cells and also, I need the pop up message for overlap dates.

    Else

    If no any overlap, then pop up messages says like “there is no overlap between selecting employees”.
    Finally I need the results to find the overlap dates to highlight so , forums experts if u have any suggestion or idea to solve this problem It will most great help for me. In case u need any more information on this problem to solve. Please let me know
    Many sites and Google I searched I could find the correct solution for this but if solved many of them looking similar to find easy method for leave plan for overlap dates.

    http://www.excelfox.com/forum/showth...ap-using-(vba)

    Pls refer the updated attachment to find for solution.
    Attached Files Attached Files
    Last edited by sathishsusa; 03-19-2017 at 03:53 PM.

  2. #2
    Junior Member
    Join Date
    Mar 2017
    Posts
    14
    Rep Power
    0
    trying by below steps can i get the code.. to get solve.

    have 4 employees, so i have 4 x DateFrom and 4 x DateTo values to deal with.

    step 1: get the input data.
    create 2 arrays. eg. myDateFrom(1 to 4) and myDateTo(1 to 4) as date arrays, then read your dates into them

    Step 2: Test for overlaps
    You need to test each date to see if the start date of any employee is >= to the start date of any other, AND <= the end date of any other employee, and vice versa for the end date. Create a boolean variable to hold the results of the test (eg myFlag)

    Step 3: Respond to a failed test
    If either the start date or the end date are disqualified (myFlag = false), run a message box and force the selection of a new date. else run any msgbox you wish.


    Sub MasterSub()
    Call step1
    Call step2
    End Sub

    Sub Step1()
    some code goes here
    End Sub

    Sub Step2()
    some code goes here
    End Sub

  3. #3
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Hi sathishsusa

    This is a very complicated requirement.

    You are asking for a complete solution _.... :-(
    _... Generally in such a Help Forum as Excel Fox, we try to help you with particular problems, and not do an entire project for you.

    But your latest attempt at explaining is good. Nice explanation of your requirement. Well done _.. :-) It was a nice clear and easy to follow File which you uploaded :-). Very Good :-)
    That made it a lot easier for me

    _.. So, I can make a start for you, based only loosely on your proposed solution.


    Here is a brief description of a code i habe done for you:
    I do not know anything about check boxes. :-(

    Rem 2) So I look at TRUE or FALSE value in Range("AS8:AS11")
    ‘2a) take in checked names data into Array
    ‘2b) from ‘2a) produce
    '2b)(i) complete initial string of overlapping names
    and
    '2b(ii)
    ( '2b)(i) add all names to initial string of overlapping names )
    and
    Build Array of all checked as True names, arrChkTrueEmp()

    Rem 3) Main Input data
    '3a) I use test data Range "B21:E26" – you will need to change as appropriate.
    '3b) use 4 arrays for each data column

    Rem 4) Main Outer loop # =====Cnt =========
    I take each name form column B data
    ‘4b) Only if I have a name in the Array of all checked as True names, arrChkTrueEmp(), do I consider the current Name , ( Else do nothing and go on to next name in column B )
    ____If I have a name in the Array of all checked as True names, Then
    ________ The Inner Loop ‘ -----CntIn-----------
    Rem 5) goes “down” all names in column B.
    _________ If a name is also in the Array of all checked as True names, arrChkTrueEmp(), the main Criteria check is done
    The criteria is either:_..
    _.. The name under consideration in the outer loop has a From date within the From and To dates of name under consideration in second inner loop
    ________ (arrFrm(Cnt, 1) >= arrFrm(CntIn, 1) And arrFrm(Cnt, 1) <= arrTo(CntIn, 1))
    Or
    _.. The name under consideration in the outer loop has a To date within the From and To dates of name under consideration in second inner loop
    ________ (arrTo(Cnt, 1) >= arrFrm(CntIn, 1) And arrTo(Cnt, 1) <= arrTo(CntIn, 1))
    _____________ If the criteria is met I put a string of "Overlap" in the Array , arrOvrLpDts(CntIn, 1). This is like your idea ...” …Create a boolean variable to hold the results of the test (eg myFlag)...”

    Rem 6) Loop through arrOvrLpDts() Array to add to report string and mark cell

    Rem 7) Message Box output of report string
    Last edited by DocAElstein; 03-20-2017 at 03:22 PM. Reason: Typos in Criteria
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  4. #4
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Results for code described in last Post. ( I have not done everything you wanted )
    ( Code is here: http://www.excelfox.com/forum/showth...0178#post10178 )

    I have not checked the code extensively. I have not done everything you wanted.
    I suggest you go through very carefully and try to understand the code and check and / or modify it to suit exactly what you want.

    _.......
    I did a quick test on your given data.

    This was your given data:

    Using Excel 2007 32 bit
    Row\Col
    AS
    AT
    7
    Check Overlap dates
    Employee names
    8
    WAHR
    employee01
    9
    FALSCH
    employee02
    10
    FALSCH
    employee03
    11
    WAHR
    employee04
    Worksheet: Sheet1


    OverLaptestData.JPG http://imgur.com/aHNfebY
    OverLaptestData.jpg

    Using Excel 2007 32 bit
    Row\Col
    B
    C
    D
    E
    18
    Employee Names
    From
    To
    OVER LAP Dates
    19
    20
    21
    employee01
    02. Jan 17
    15. Jan 17
    22
    employee03
    03. Feb 17
    10. Feb 17
    23
    employee04
    10. Jan 17
    20. Jan 17
    24
    employee04
    04. Feb 17
    10. Feb 17
    25
    employee02
    01. Mrz 17
    05. Mrz 17
    26
    employee04
    03. Mrz 17
    10. Mrz 17
    Worksheet: Sheet1



    Here is the results after running my code:( Check boxes checked in AS8 and AS11 to make cell value TRUE . Range object "behind" the cells has that entry pseudo as Range object and the True held As a Boolean , ...)

    Using Excel 2007 32 bit
    Row\Col
    B
    C
    D
    E
    18
    Employee Names
    From
    To
    OVER LAP Dates
    19
    20
    21
    employee01
    02. Jan 17
    15. Jan 17
    22
    employee03
    03. Feb 17
    10. Feb 17
    23
    employee04
    10. Jan 17
    20. Jan 17
    24
    employee04
    04. Feb 17
    10. Feb 17
    25
    employee02
    01. Mrz 17
    05. Mrz 17
    26
    employee04
    03. Mrz 17
    10. Mrz 17
    Worksheet: Sheet1


    And Message Box:
    MsgBoxOverlaps.JPG http://imgur.com/3XX7PKT
    MsgBoxOverlaps.JPG
    Last edited by DocAElstein; 03-20-2017 at 03:31 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  5. #5
    Junior Member
    Join Date
    Mar 2017
    Posts
    14
    Rep Power
    0
    Hi Docaelstin,
    Thanks you so much for your hard work. I know what i ask task is too complicated and I am sorry for taking your time for me. Iam very happy to see your post. I m checking now in my file I will go through it and I will reply to you.

  6. #6
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Hi sathishsusa :-)

    Thanks for the feedback.

    Try to understand and if / as necessary modify the code yourself as much as possible .

    But if you still have problems, or need anything explaining then I will try to help further when I have time

    Alan
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  7. #7
    Junior Member
    Join Date
    Mar 2017
    Posts
    14
    Rep Power
    0
    Thanks Doc i will do it what you said perfect awesome i will closed the thread.

  8. #8
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Hi sathishsusa
    Quote Originally Posted by sathishsusa View Post
    i will closed the thread.
    I think you probably mean “Mark Thread as SOLVED”
    But
    There is no facility to mark threads as solved at ExcelFox
    Threads are not marked as solved at ExcelFox

    There is no need for you to take any action in that respect. – You do not need to take any such action when a Thread is solved.
    You do not need to do anything
    Alan




    Edit
    P.s
    .
    Thanks for the Rep and nice Rep Comments :-) ( By the way, Rep Comments can only be seen by those reciving them - No one else can see them. They are similar to a Private Message. )
    Last edited by DocAElstein; 03-20-2017 at 02:20 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  9. #9
    Junior Member
    Join Date
    Mar 2017
    Posts
    14
    Rep Power
    0
    Thanks alan

  10. #10
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Quote Originally Posted by sathishsusa View Post
    Thanks alan
    You is welcome
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. Replies: 2
    Last Post: 03-20-2017, 02:31 PM
  2. Displaying dates in reports as MM/DD
    By papabill in forum Access Help
    Replies: 1
    Last Post: 02-26-2015, 06:16 AM
  3. Insert Missing Dates By Comparing Two Lists Of Dates
    By mahmoud-lee in forum Excel Help
    Replies: 24
    Last Post: 10-16-2013, 04:48 PM
  4. How to Extracting dates and days between 2 dates.
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 9
    Last Post: 08-11-2012, 09:11 PM
  5. Replies: 7
    Last Post: 06-23-2012, 07:56 PM

Tags for this Thread

Posting Permissions

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