1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vacation and Payroll - Date Overlap

Discussion in 'Ask an Excel Question' started by david gabra, Nov 30, 2018.

  1. david gabra

    david gabra Member

    Messages:
    166
    Hi

    I have attached a file with two tabs

    Master: this show the i) employee# (column A) ii) cut-of payroll date (column B) - this is date that the payroll is paid for that month. ii)Amount paid to the employee.

    Vacation: Two columns: the i) employee# (column A) ii) the dates vacation taken (column B and C)

    My goals is to see if an person was paid during the time they were on holiday and the number of vacation days that appear in the payroll month.

    I added a manual worked example, one additional column "Was a Vacation Taken" and for this I would like a "yes" or "no"

    I have added a notes column to explain the reason behind what numbers should appear.

    I hope that is clear.

    Having this automated would be amazing.

    Thank you

    David.

    Attached Files:

  2. charlesdh

    charlesdh Member

    Messages:
    46
    Hi,
    May not have a answer for you. But, couldn't you add 2 column's in the Master for the vacation time "From" "To"?
    It would be easier to see what was taken.

    Charles
  3. david gabra

    david gabra Member

    Messages:
    166
    Hi
    I want to repeat this exercise many time and I need to know the number of days that each event occurs in the month.
    I have to do this for 1000s of employees. I
    I really need something automated if someone can help me.
    thank you
  4. vletm

    vletm Excel Ninja

    Messages:
    4,415
    david gabra
    You have had many this kind of dates cases ...
    Test this too ... Press [ Do It ] in Cell G1
    Be careful, that all Your correct data are valid and as in Your sample file!

    Attached Files:

  5. david gabra

    david gabra Member

    Messages:
    166
    thank you - it work great.

    I edited the macro and changed the cell from F to H -and whatever I do - it always added to column F -why???

    Code (vb):

    Sub Rectangle1_Click()

        On Error Resume Next
        Application.ScreenUpdating = False
        With Sheets("Master")
            .Range("H:H").ClearContents
            .Range("H1") = "Next Column of Number Days"
        End With
        With Sheets("Vacation")
            .Range("D:D").ClearContents
            a = 2
     
    It still show the number of column FF like in your macro -
    I tried 10 times, everything I can think of.
    what am I doing wrong? I don't get it.

    I attached a copy - just in case you want to see.

    Attached Files:

    Last edited by a moderator: Dec 2, 2018
  6. vletm

    vletm Excel Ninja

    Messages:
    4,415
    david gabra
    1st You wrote that 'it work great'.
    and
    next You start to edit .. someway ... hmm?
    > Why You didn't send sample file which is as same as Your needed? <

    If You edit as You've sent short part of code ... hmm?
    ... what would You think to need this time?
    If You 'clean' other columns (F to H) values,
    ... then that's all You've edited
    and that's all what it would do!

    Excel is someway great ---
    it'll do, just as someone has asked to do.
    ... but many times, someone haven't asked Excel to do needed things.
    ... what has someone would like to see or get.


    > Everything seems to be 'okay'
    > ... even Excel don't know what else it would do.

    Did You edit something else
    which would give a hint for Excel ... what to do?
    sureshsonti likes this.
  7. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    The attached workbook might provide a start for a formula-based solution.
    Each output is specific to a particular employee and month so I developed the formula for a specific row and, once done, allowed the references to become row-relative.

    To attribute a vacation period to a month one can check the vacation bound (start or return to work) against the month start and end with 3 cases emerging [before, during, after]. The start and end can be checked using the same formula, i.e. 'case', refers to
    = 1 + (vacationBounds>monthStart) + (vacationBounds>monthEnd)
    where 'vacationBounds' is
    = vacation + {0,1}

    The duration of any overlap requires an (end - start) calculation that can be performed using MMULT, i.e. 'duration' is given as
    = MMULT( (takenBy=ID) * CHOOSE( case, monthStart, vacationBounds, monthEnd ), {-1;1} )

    The worksheet formulae are
    = SUM( duration )
    and
    = IF(numberDays, "Yes", "No" )

    Attached Files:

  8. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    Evaluation:

    Although the method of #7 should give the results you require, there may be major performance issues once one gets to 1000s of employee IDs running over tens of 1000s of vacation periods. Optimisation for speed could become a major issue.

    One approach would be to sort the vacation record by employee ID, thus limiting the range of any aggregation. If that is not possible, it may be beneficial to limit all calculation to within a preset maximum time interval. That would be effective for vacation but less so for sick leave, which could, in theory, span many months.
  9. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    Just noticed an oddity in the documentation. Because the actual location of data within the workbook is of little significance to me, I documented the defined name 'vacation' as simply 'Absolute reference' rather than bothering with
    =Vacation!$C$2:$D$5

    At least, that is what I intended; a touch of finger trouble and the documentation actually read
    "Absolute reverence"

    May I assure you that "Absolute reverence" is NOT expected! :oops:

    Attached Files:

  10. charlesdh

    charlesdh Member

    Messages:
    46
    Hum,
    Does the user include "Weekend" or "Holidays" for the number of days taken?

    Charles
  11. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    charlesdh
    Yes. My guess is that is the way things are going. I can see
    = NETWORKDAYS.INTL( +startVacation, +returnWork)
    on the horizon. So far there is no indication of that in the requirements though.

    By the way, do you or anyone else, know the pedigree of these strange date functions that get indigestion when faced with a multi-cell ranges in place of single-cell parameters?

    p.s. I think the following will work
    = NETWORKDAYS.INTL( +startVacation, +returnWork, 1,INDEX(holidays, k, {1,2,3}) )
    where k is the month index and up to three holidays may be defined for each month. These are areas that I do not see in my normal work, though.
  12. david gabra

    david gabra Member

    Messages:
    166
    this is great - it does the job-
    I don't really understand how it works - but it does - thanks a lot!!!!!!
    I am trying to add more more lines of data, and I get errors.
    I tried changing named ranges and more errors.
    Can we make it so that it is already setup for any data added
    Last edited: Dec 6, 2018
  13. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    Hi david
    If you are going to use the workbook I should forego a touch of 'elegance' and make the formulas easier to follow, though they will be longer.

    Meanwhile I have made the names referring to absolute references dynamic by introducing tables which can be extended either by dragging the 'fly speck' in the bottom right corner of each table down as required or by hitting tab with the final cell of the table selected. This will automatically adjust the defined names which are now based upon structured references.

    If you want to avoid counting weekends then let me know.

    Attached Files:

  14. charlesdh

    charlesdh Member

    Messages:
    46
    David,
    Does the file you have posted represent your actual file?
    Are you tracking the number of day's taken?

    Charles
    Last edited: Dec 8, 2018 at 9:00 PM
  15. david gabra

    david gabra Member

    Messages:
    166
    r of day's taken?

    Charles[/quote]

    This is a dummy file for confidentiality reasons
    I am tracking the numbers of days taken.
  16. david gabra

    david gabra Member

    Messages:
    166
    -- you raise a very interesting point. If I only want to count weekends - how can this be amended - there are other regulations - thank you very much.
  17. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    I would deal with the issue at the point at which the SSN is typed, copied or written from a program into the worksheet.

    Code (vb):
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim SSNcell As Range
        'Test whether content should be an abbreviated SSN
       'This restricts the area of application of the event handler
       If Not Intersect(Target, Range("SSN")) Is Nothing Then
            'Make sure the program does not trigger a further event
           Application.EnableEvents = False
            'Loop over intersection
           For Each SSNcell In Intersect(Target, Range("SSN"))
                SSNcell.Value = VBA.Right(SSNcell.Value, 4)
            Next
            'Reset
           Application.EnableEvents = True
        End If
    End Sub
    The US Special number format will insert the apparent zeros and hyphens.
  18. david gabra

    david gabra Member

    Messages:
    166
    I have no idea what you wrote :(
  19. charlesdh

    charlesdh Member

    Messages:
    46
    This is a dummy file for confidentiality reasons
    I am tracking the numbers of days taken.[/quote]

    I realize that it was a dummy file. I just wanted to know if the file that you use is formate'd like the file you have posted.
    In the past I did help a user that tracked the "Vt", "Sick" time. It also generated a "Letters" from "Council" up to "Dismissal".
  20. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    I see your point! I posed in a hurry on the wrong thread! :oops: Just missed the time slot for delete :(
  21. david gabra

    david gabra Member

    Messages:
    166
    Hi
    I tried with the real data and the number do not add up

    this is the date range to check


    ID From Date To Date

    9089 May 14, 2018 September 14, 2018


    9089 May 1, 2018 9836.17 No 17
    9089 June 1, 2018 9837.17 No 106
    9089 July 1, 2018 9838.17 No
    9089 August 1, 2018 9839.17 No


    it does not make sense that there are 106 days for June - the days should be split by the months below.

    Can you help amend

    Thanks

    Dave.

Share This Page