• 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...

  • 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

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.
 

Attachments

  • Vacation and Payments.xlsx
    16.6 KB · Views: 16
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
 
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
 
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!
 

Attachments

  • Vacation and Payments.xlsb
    20.8 KB · Views: 12
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:
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.
 

Attachments

  • Vacation and Payments 2-.xlsb
    23.1 KB · Views: 5
Last edited by a moderator:
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?
 
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" )
 

Attachments

  • Vacation and Payments (PB).xlsx
    19.1 KB · Views: 13
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.
 
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:
 

Attachments

  • Vacation and Payments (PB).xlsx
    18.9 KB · Views: 16
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.
 
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:
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.
 

Attachments

  • Vacation and Payments (dynamic).xlsx
    17.7 KB · Views: 12
David,
Does the file you have posted represent your actual file?
Are you tracking the number of day's taken?

Charles
 
Last edited:
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.

-- 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.
 
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:
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.
 
r of day's taken?

Charles

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".
 
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.
 
Back
Top