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

Need to find out the last punching date before an employee went on leave and the first punching date after the leave

A

anishms

Member
Need help in identifying the last punching date before an employee went on leave and first punching date after the same employee joined back after leave. I have the date wise punching details and start and end date of leaves as given below.
Can somebody help me with the formula to retrieve the dates. In the following example, I need 11-Sep-19 as the last punching before leave and 23-Oct-19 as the first punching after the leave

64240
 

Attachments

  • For Forum.xlsx
    325.6 KB · Views: 0
Last edited:
vletm said:
anishms
with steps ...
Click to expand...
Thanks for the quick response. But I need to apply this in a large data where the same employees have taken multiple leaves.
I have attached a month data for your reference. I need the date sin column F & G as highlighted in yellow
 

Attachments

  • For Forum.xlsx
    325.6 KB · Views: 2
anishms
... But I need to apply this in a large data ...
How many rows data did Your original sample has?
If You ask an answer for one ... yes
 
Hi ,

If you can upload a workbook which has much more data , say a thousand rows , it will help to test a formula thoroughly.

Narayan
 
NARAYANK991 said:
Hi ,

If you can upload a workbook which has much more data , say a thousand rows , it will help to test a formula thoroughly.

Narayan
Click to expand...
Your first formula is working perfectly and this is a second condition. I have attached the some more data. If you can check and revert it will be very helpful. I need the days gap in column H and I
 

Attachments

  • Chandoo.xlsx
    367.1 KB · Views: 6
anishms
Layouts have to keep same after Your copy!
Only You'll know 'the following cases' ... if challenges then You should give exact details - what?
 
vletm said:
anishms
Layouts have to keep same after Your copy!
Only You'll know 'the following cases' ... if challenges then You should give exact details - what?
Click to expand...

When I copy the complete data and do it, it is showing some wrong figures. Leaves sheet has total 1200 rows and punching sheet has total 50000 rows data. I'm unable to upload the file due to large file size. Its ok, I can do it different batches. But please check the following cases
64258
 
vletm said:
anishms
It would be much better to have some kind of clear logic then someone will add new lines.
Click to expand...
Hi ,

I will explain.

Consider the following entries for one person.

140398
0.5556​
Festival Leave21-Apr-201921-Apr-201918-Apr-201922-Apr-2019
140398
1​
Sick Leave07-Aug-201907-Aug-201906-Aug-201908-Aug-2019
140398
1​
Sick Leave20-Aug-201920-Aug-201919-Aug-201924-Aug-2019
140398
1​
Obsolete Compensatory Leave21-Aug-201921-Aug-201919-Aug-201924-Aug-2019
140398
1​
Compensatory Leave12-Sep-201912-Sep-201911-Sep-201923-Oct-2019
140398
5​
Obsolete Compensatory Leave15-Sep-201919-Sep-201911-Sep-201923-Oct-2019
140398
30​
Annual Leave21-Sep-201920-Oct-201911-Sep-201923-Oct-2019
140398
2​
Unpaid Leave21-Oct-201922-Oct-201911-Sep-201923-Oct-2019

The last date when there was a punch before the person went on leave , as well as the earliest date when there was a punch after the person came back from leave is already available in the last two columns on the right.

We have two situations here ; the first two entries fall in the first situation where two entries are independent of each other. Here there is no problem since the gap before leave as well as the gap after leave is simple arithmetic.

The second situation is where we have multiple entries of leave with the same punch dates.

Let us take the last 4 entries , all of which have the same last punch date before leave (11-Sep-2019) and the same earliest punch date after leave (23-Oct-2019).

To get the actual gap between the punch before leave and the leave , we consider the difference between 12-Sep-2019 and 11-Sep-2019 , which is zero days for the first entry.

It can either be zero for the remaining 3 entries or it can be the difference between each of the leave start dates 15-Sep-2019 , 21-Sep-2019 and 21-Oct-2019 and the punch date before leave of 11-Sep-2019. OP will clarify this , but again the calculation is simple arithmetic.

The problem comes in calculating the gap after leave.

Since the punch date after leave is 23-Oct-2019 , and the last leave ended on 22-Oct-2019 , the difference between these two dates is zero days.

Now , we are left with only the calculations for the days within the leave periods.

Between the ending of the first leave period on 12-Sep-2019 and the beginning of the second leave period on 15-Sep-2019 , there is no intervening punch date , and so this is a gap of 2 days.

Between the ending of the second leave period on 19-Sep-2019 and the beginning of the third leave period on 21-Sep-2019 , there is no intervening punch date , and so this is a gap of 1 day.

Between the ending of the third leave period on 20-Oct-2019 and the beginning of the fourth leave period on 21-Oct-2019 , there is no intervening punch date , and so this is a gap of zero days.

If you have any doubts , please ask.

Narayan
 
NARAYANK991 said:
Hi ,

I will explain.

Consider the following entries for one person.

140398
0.5556​
Festival Leave21-Apr-201921-Apr-201918-Apr-201922-Apr-2019
140398
1​
Sick Leave07-Aug-201907-Aug-201906-Aug-201908-Aug-2019
140398
1​
Sick Leave20-Aug-201920-Aug-201919-Aug-201924-Aug-2019
140398
1​
Obsolete Compensatory Leave21-Aug-201921-Aug-201919-Aug-201924-Aug-2019
140398
1​
Compensatory Leave12-Sep-201912-Sep-201911-Sep-201923-Oct-2019
140398
5​
Obsolete Compensatory Leave15-Sep-201919-Sep-201911-Sep-201923-Oct-2019
140398
30​
Annual Leave21-Sep-201920-Oct-201911-Sep-201923-Oct-2019
140398
2​
Unpaid Leave21-Oct-201922-Oct-201911-Sep-201923-Oct-2019

The last date when there was a punch before the person went on leave , as well as the earliest date when there was a punch after the person came back from leave is already available in the last two columns on the right.

We have two situations here ; the first two entries fall in the first situation where two entries are independent of each other. Here there is no problem since the gap before leave as well as the gap after leave is simple arithmetic.

The second situation is where we have multiple entries of leave with the same punch dates.

Let us take the last 4 entries , all of which have the same last punch date before leave (11-Sep-2019) and the same earliest punch date after leave (23-Oct-2019).

To get the actual gap between the punch before leave and the leave , we consider the difference between 12-Sep-2019 and 11-Sep-2019 , which is zero days for the first entry.

It can either be zero for the remaining 3 entries or it can be the difference between each of the leave start dates 15-Sep-2019 , 21-Sep-2019 and 21-Oct-2019 and the punch date before leave of 11-Sep-2019. OP will clarify this , but again the calculation is simple arithmetic.

The problem comes in calculating the gap after leave.

Since the punch date after leave is 23-Oct-2019 , and the last leave ended on 22-Oct-2019 , the difference between these two dates is zero days.

Now , we are left with only the calculations for the days within the leave periods.

Between the ending of the first leave period on 12-Sep-2019 and the beginning of the second leave period on 15-Sep-2019 , there is no intervening punch date , and so this is a gap of 2 days.

Between the ending of the second leave period on 19-Sep-2019 and the beginning of the third leave period on 21-Sep-2019 , there is no intervening punch date , and so this is a gap of 1 day.

Between the ending of the third leave period on 20-Oct-2019 and the beginning of the fourth leave period on 21-Oct-2019 , there is no intervening punch date , and so this is a gap of zero days.

If you have any doubts , please ask.

Narayan
Click to expand...
Yes, absolutely this is the issue which I highlighted before in another case. You have explained it in very detailed way. Thanks for your time and the detailed explanation.
Now can we write a formula to identify the net difference considering the splitting of leaves or we will have to check it manually?
 
anishms
As I wrote:
It would be much better to have some kind of clear logic then someone will add new lines
Below is sample of result without logic.
the 1st row has 'Person number's
A-column has dates
Yellows are 'Leaves' - the 1st day of EACH leave has comment
Times are 'Punching'-times
Screenshot 2019-12-01 at 10.47.51.png
As You could notice ... someone has 'sometime' done punches while Leave ...
... if there are Night Shifts ... then some Out-punches ... hmm?
 
I wror
NARAYANK991 said:
Hi ,

I will explain.

Consider the following entries for one person.

140398
0.5556​
Festival Leave21-Apr-201921-Apr-201918-Apr-201922-Apr-2019
140398
1​
Sick Leave07-Aug-201907-Aug-201906-Aug-201908-Aug-2019
140398
1​
Sick Leave20-Aug-201920-Aug-201919-Aug-201924-Aug-2019
140398
1​
Obsolete Compensatory Leave21-Aug-201921-Aug-201919-Aug-201924-Aug-2019
140398
1​
Compensatory Leave12-Sep-201912-Sep-201911-Sep-201923-Oct-2019
140398
5​
Obsolete Compensatory Leave15-Sep-201919-Sep-201911-Sep-201923-Oct-2019
140398
30​
Annual Leave21-Sep-201920-Oct-201911-Sep-201923-Oct-2019
140398
2​
Unpaid Leave21-Oct-201922-Oct-201911-Sep-201923-Oct-2019

The last date when there was a punch before the person went on leave , as well as the earliest date when there was a punch after the person came back from leave is already available in the last two columns on the right.

We have two situations here ; the first two entries fall in the first situation where two entries are independent of each other. Here there is no problem since the gap before leave as well as the gap after leave is simple arithmetic.

The second situation is where we have multiple entries of leave with the same punch dates.

Let us take the last 4 entries , all of which have the same last punch date before leave (11-Sep-2019) and the same earliest punch date after leave (23-Oct-2019).

To get the actual gap between the punch before leave and the leave , we consider the difference between 12-Sep-2019 and 11-Sep-2019 , which is zero days for the first entry.

It can either be zero for the remaining 3 entries or it can be the difference between each of the leave start dates 15-Sep-2019 , 21-Sep-2019 and 21-Oct-2019 and the punch date before leave of 11-Sep-2019. OP will clarify this , but again the calculation is simple arithmetic.

The problem comes in calculating the gap after leave.

Since the punch date after leave is 23-Oct-2019 , and the last leave ended on 22-Oct-2019 , the difference between these two dates is zero days.

Now , we are left with only the calculations for the days within the leave periods.

Between the ending of the first leave period on 12-Sep-2019 and the beginning of the second leave period on 15-Sep-2019 , there is no intervening punch date , and so this is a gap of 2 days.

Between the ending of the second leave period on 19-Sep-2019 and the beginning of the third leave period on 21-Sep-2019 , there is no intervening punch date , and so this is a gap of 1 day.

Between the ending of the third leave period on 20-Oct-2019 and the beginning of the fourth leave period on 21-Oct-2019 , there is no intervening punch date , and so this is a gap of zero days.

If you have any doubts , please ask.

Narayan
Click to expand...

I wrote a formula to find out the net difference in days considering the splitting of leaves and seems to be working. Could you please check
 

Attachments

  • Chandoo (1).xlsx
    368 KB · Views: 2
You must log in or register to reply here.
Back
Top