# Adjust the equation to determine the work Shift

#### 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 PM TO 7:00 AM First Shift 12:00 AM TO 3:00 PM Second Shift 8:00 AM TO 11:00 PM Third 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

• 950.1 KB Views: 9
• 167.7 KB Views: 8

#### NARAYANK991

##### Excel Ninja
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

#### Hany ali

##### Active Member
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

• 142.8 KB Views: 7

#### vletm

##### Excel Ninja
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.

#### NARAYANK991

##### Excel Ninja
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

#### Hany ali

##### Active Member
thanks mr.
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

#### Hany ali

##### Active Member
thanks mr.
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:

#### vletm

##### Excel Ninja
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 )

#### Hany ali

##### Active Member
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

#### vletm

##### Excel Ninja
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.

#### vletm

##### Excel Ninja
Hany ali
What would You mean?
At least, I asked You to upload VALID RAW DATA.
After that I could ask more details.

#### NARAYANK991

##### Excel Ninja
Hi ,

See if this is OK.

Narayan

#### Attachments

• 992.8 KB Views: 6

#### vletm

##### Excel Ninja
Hany ali
I tried to ask two times VALID RAW DATA which could use ...

I found three mystic columns ...
Do You have any ideas ... what?

#### Hany ali

##### Active Member
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

• 991.6 KB Views: 2
• 159.2 KB Views: 2

#### vletm

##### Excel Ninja
Hany ali
So, You couldn't send a file which has VALID RAW COLUMNS A & B.
You're that challenge ...

#### Hany ali

##### Active Member
as you mean just in this file COLUMNS A & B.

#### Attachments

• 208.4 KB Views: 2

#### NARAYANK991

##### Excel Ninja
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

#### Hany ali

##### Active Member
thanks mr
NARAYANK991

it's not enough equation in the column L To be Correct

#### NARAYANK991

##### Excel Ninja
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

#### vletm

##### Excel Ninja
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!

#### Attachments

• 65.8 KB Views: 5

#### Hany ali

##### Active Member
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

#### NARAYANK991

##### Excel Ninja
but still when i have more than one Attendance in the same date for the same employee ,we have some mistake in this formula
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

#### vletm

##### Excel Ninja
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!

#### NARAYANK991

##### Excel Ninja
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 ?
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

#### vletm

##### Excel Ninja
Hany ali
Did You skip my writings #23 reply? or what?
1) raw valid data
2) VBA