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

Adjust the equation to determine the work Shift

H

Hany ali

Active Member
Hello every one ,i want your help in determine the work Shift ,because we have fixed time for all shift
3 Shift IN The Work
4:00 PMTO7:00 AMFirst Shift
12:00 AMTO3:00 PMSecond Shift
8:00 AMTO11:00 PMThird Shift
Code: 
=IF(AND(G2>=$X$5,$L2=$Z$3),$Y$5,IF(AND(G2<=$V$5,$L2=$Z$4),$Y$5,IF(AND(G2>=$X$7,$L2=$Z$3),$Y$7,IF(AND(G2<=$V$7,$L2=$Z$4),$Y$7,""))))
 

Attachments

  • Finger Sheet .xlsx
    950.1 KB · Views: 10
  • Untitled.png
    Untitled.png
    167.7 KB · Views: 10
Hi ,

Can you explain what your requirement is ?

The formula you want adjusted depends on the formula in column L ; tracing the logic of your requirement based on multiple formulas is not as easy as developing the required formulas based on your requirement logic if you can explain that clearly and comprehensively.

Narayan
 
thanks sir for your Reply
yes adjusted depends on the formula in column L and column G For Timing ,it should be as per data for our Work Shift timing,
and we get this Data from Fingerprint device without kind of Work Shift,it should be to know which work shift -to how to count work hours and late time and overtime for all ,to Reward or punishment
because we have tree shift as following :
*First shift start from 07:00 Am Till 04:00 Pm
*Second Shift Start from 03:00 Pm till 12:00 Am
*Third Shift Start From 11:00 Pm till 08:00 Am
this Result Manuel As Example:
 

Attachments

  • Untitled.png
    Untitled.png
    142.8 KB · Views: 8
Hany ali
If someone comes to work eg 15:30. Which shift is it?
... if same someone should work the 1st shift. Is someone 8 hrs 30 min 'late'?
... if same someone should work the 2nd shift. Is someone 30 min 'late'?
Especially then the Out-timestamp is missing for some reason.
 
Hi ,

Can you explain the following :

1. I would like to start with only the input data , and as far as I can see , only column D fits this definition.

2. What is the relevance of the data in column E , and why are there so many consecutive Clock ins ? Do we ignore this column ?

3. If we ignore the data in column E , do we take that clock ins and clock outs alternate ? Thus , starting with a clock in , in cell D2 , D3 is a clock out , D4 is a clock in , D5 is a clock out and so on for the rest of the data ?

4. If there is a time stamp of 3:40 , if it is a clock out , it is an exit in the first shift , but 20 minutes early ; if it is a clock in , it is an entry , but 40 minutes late. Is this correct ?

Narayan
 
thanks mr.
vletm for your Reply
If someone comes to work eg 15:30. Which shift is it?
if it's Attendance which it found in column L ,it should be Second Shift,but if it's Leave ,it should be First Shift
 
thanks mr.
NARAYANK991 for your Reply
if you can Depends For Column D ,OK NO Problem you can to make it
and about column E it's not correct ,for this Reason I mak formula in Column L
YES IT should to ignore the data in column E
Code: 
=IF(COUNTIFS($F$2:F2,F2,$C$2:C2,C2)=1,"Attendance1",IF(COUNTIFS($F$2:F2,F2,$C$2:C2,C2)=2,"Leave 1",IF(COUNTIFS($F$2:F2,F2,$C$2:C2,C2)=3,"Attendance2",IF(COUNTIFS($F$2:F2,F2,$C$2:C2,C2)=4,"Leave 2",""))))

if there is a time stamp of 3:40 , if it is a clock out , it is an exit in the first shift , but 20 minutes early ; if it is a clock in , it is an entry , but 40 minutes late. Is this correct ?
yes of course correct ,but if IN ,it Should be in Second Shift Not First Shift
I hope every thing is Clear Now
 
Last edited:
Hany ali
If someone comes to work eg 15:30. Which shift is it?
For me, if someone COMES TO work, then someone will make SHIFT IN TIMESTAMP!
You answered that the 1st or the 2nd shift ... that's not possible!

Do those employees have plan (or something) when those should work?
Your way, means that employees could work whenever those would like to work! ... or how?

Do Fingerprint device have option to make stamp IN or OUT? ( ... or there is ONE 'button' to take care both )
 
thanks sir
exactly employees can change the shift as they want ,for this Reason it's problem to me to count for them work Hours without I know From the beginning which WORK shift
IN Or OUT IN MY Fingerprint device NOT Correct
 
Hany ali
Hmm ... employees can work whenever ... okay.
If same time Fingerprint device do not give correct IN /OUT data and as normally there are missing stamps
... then You'll have a challenge!
Please, modify Your file and left there ONLY VALID RAW DATA.
 
very good sir
mr.
NARAYANK991

but when i have more than one Attendance in the same date for the same employee ,we have some mistake in this formula as you see
 

Attachments

  • SampleData.xlsx
    991.6 KB · Views: 3
  • Untitled.png
    Untitled.png
    159.2 KB · Views: 2
Hi ,

I am sorry , but based only on the timing data , it is impossible to be 100% correct reliably.

There has to be additional data on whether a time is an IN time or an OUT time ; this is all the more necessary because the shift times overlap.

Narayan
 
Hi ,

The formula in column L is also a calculation ; in the absence of confirmatory data , you will never know whether the calculation is correct or not.

In the uploaded file , if we look at rows 69 downwards , there is no clarity on whether someone has done 2 shifts or is a time punch missing.

Narayan
 
Hany ali
I asked VALID RAW DATA - Your file is something else.
I tried to do steps for You = NOT READY formula!
1) solve timeparts from B-column values
2) solve D-column values - use only values which could be NORMAL workshift length eg 6-11hrs ! OF course, with same employee!
3) solve E-column values = SHIFT ... as written ... those 2hr gaps gives needed magic
If You would have real VALID RAW DATA
and
if there would be more fingerprints than those 99 - I would use VBA for this!
Questions? ... answers?
 

Attachments

  • Finger Sheet 2.xlsb
    65.8 KB · Views: 6
thanks alot elso good idea
but still when i have more than one Attendance in the same date for the same employee ,we have some mistake in this formula
and when i have more than 500 row ,This equation is difficult to apply
 
Hany ali said:
but still when i have more than one Attendance in the same date for the same employee ,we have some mistake in this formula
Click to expand...
Hi ,

If your attendance data contains just two items :

1. Date and time stamp

2. Whether it is an IN punch or an OUT punch

a formula can be given which will be 100% accurate and reliable. However many times an employee punches IN or OUT on the same date , and even at the same time.

In the absence of the above 2 items , no formula will ever be 100% accurate and reliable.

Narayan
 
Hany ali
If You won't upload valid raw data then a challenge.
You're writing 'some mistakes' ... then You should name those.
If You're are working with more rows then these should solve with VBA - seems that You skip some hints - or You'll need looooong formulas.
If Your company's policy is as free as You've written - then there will be some challenges.
With Your fingerprint-data is possible solve this - but - there should be clear rules!
 
Hany ali said:
already my attendance data contains just two items :

1. Date and time stamp ,in Column B
Date/Time
01-Nov-19 7:22 AM​
Do you mean that the type of work shift is explained previously ?
Click to expand...
Hi ,

No. I have mentioned the two items of data that are needed.

1. The date and time stamp - that is just one item of data , since a time stamp is of no use without the date also in it.

The second item of data which is required is whether a date and time stamp data is an IN punch or an OUT punch.

In your case , a time stamp such as 3:30 P.M. can be either an OUT punch from an employee in the first shift , or an IN punch from an employee in the second shift.

There is no way that any calculation , however good it is , can be 100% accurate and reliable. Only actual data from the machine itself can help.

If these two items of data are available , the type of shift can easily be derived.

Narayan
 
You must log in or register to reply here.
Back
Top