Monitor Late Arrivals

[Lasantha]

Dear Team,

Kindly look into the attached data file which contains IN and OUT times of employees by shift and department.

I need your help to capture Late arrivals and Early Exit. (Column F and G)

we have two shift, Day and night. Day shift starts at 8:40:00 AM and Night shift starts at 7:10:00 PM, those who come after this times should be mark as "Late" in column F.

And Day shift ends at 5:30:00 PM and Night shift 4:00:00 AM(next day) , those who leave before this time should be mark as "Early exit" in column G.

There are few managers who have Straddle shift, this will not applicable for them.

Could you please help me to create a formula for this.

Thank you.

 

[AlanSidman]

In F2 =IF(AND(C2="Day",D2>$J$3),"Late",IF(AND(C2="Night",D2>J4),"Late","")) and copy down

In G2 =IF(AND(Table1[@Shift]="Day",E2<$K$3),"Early Exit",IF(AND(Table1[@Shift]="Night",E2<$K$4),"Early Exit","")) and copy down

 

[Logit]

Alan:

The G2 formula is throwing an error at the first : Table1[@Shift]

Or is this something that is not available in Excel 2007 ... that I am running ?

 

[kamal bhatt]

Alan:

The G2 formula is throwing an error at the first : Table1[@Shift]

Or is this something that is not available in Excel 2007 ... that I am running ?

Replace AND(Table1[@Shift] by C2, so the new formula would be
=IF(AND(C2="Day",D2>$J$3),"Late",IF(AND(C2="Night",D2>$J$4),"Late",""))

 

[bosco_yip]

In F2 =IF(AND(C2="Day",D2>$J$3),"Late",IF(AND(C2="Night",D2>J4),"Late","")) and copy down

In G2 =IF(AND(Table1[@Shift]="Day",E2<$K$3),"Early Exit",IF(AND(Table1[@Shift]="Night",E2<$K$4),"Early Exit","")) and copy down

For the above copy down formula, using one IF function is enough and could return the same result.

In [F2] =IF((C2="Day")*(D2>$J$3)+(C2="Night")*(D2>$J$4),"Late","")

In [G2] =IF((C2="Day")*(E2<$K$3)+(C2="Night")*(E2<$K$4),"Early Exit","")

Regards

 

[Lasantha]

For the above copy down formula, using one IF function is enough and could return the same result.

In [F2] =IF((C2="Day")*(D2>$J$3)+(C2="Night")*(D2>$J$4),"Late","")

In [G2] =IF((C2="Day")*(E2<$K$3)+(C2="Night")*(E2<$K$4),"Early Exit","")

Regards

Thank you

 

[Lasantha]

Replace AND(Table1[@Shift] by C2, so the new formula would be
=IF(AND(C2="Day",D2>$J$3),"Late",IF(AND(C2="Night",D2>$J$4),"Late",""))

Thank you

 

[Lasantha]

In F2 =IF(AND(C2="Day",D2>$J$3),"Late",IF(AND(C2="Night",D2>J4),"Late","")) and copy down

In G2 =IF(AND(Table1[@Shift]="Day",E2<$K$3),"Early Exit",IF(AND(Table1[@Shift]="Night",E2<$K$4),"Early Exit","")) and copy down

Thank you.