Attendance Sheet

[uswyne]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]A2[/TD]
[TD]A3[/TD]
[TD]A4[/TD]
[TD]A5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Day[/TD]
[TD]Time In[/TD]
[TD]Time Out[/TD]
[TD]Attendance[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Usman[/TD]
[TD]Saturay[/TD]
[TD]9:00:59[/TD]
[TD]14:00:59[/TD]
[TD]Present[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sunday[/TD]
[TD]off Day[/TD]
[TD]off Day[/TD]
[TD]Present[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Monday[/TD]
[TD]7:30:59[/TD]
[TD]15:30:59[/TD]
[TD]Present[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Tuesday[/TD]
[TD]7:31:00[/TD]
[TD]15:30:59[/TD]
[TD]Late coming[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

if A2 Saturday and A3 <=9:00:59, A4>=14:00:00 than A5, "Present" otherwise "Late Coming"
and if A3 Or A4 are blank than "Absent" and if A4 if <14:00 than "Early going"

if A2 Sunday than A3 & A4 blank than A5, "Off Day"

if A2 Weekdays A3 <=7:30:59, A4>=15:30:59 than A5, "Present" otherwise "Late Coming"
and if A3 Or A4 are blank than "Absent" and if A4 if <15:30 than A5 "Early going"

Kindly help

 

[Kamal GKJ]

Hi Uswyna:

I Have a doubt whether there should be a single formula or multiple can be applied..



For first condition please try this formula {=IF(A23="Saturday",IF(AND(A33<=TIMEVALUE("09:00:59"),A43>=TIMEVALUE("14:00:59")),"Present","Late Commer"),"Other Working Days")}

 

[uswyne]

that what i use for this

=if(A3="","Absent",If(A4="","Absent",if(A1="Saturday",(if(A3>=time(9,0,59),"late coming","Present")),(if(A3>=time(7,50,59),"late coming","Present")))


But i am unable to fix early going.

Hi Uswyna:

I Have a doubt whether there should be a single formula or multiple can be applied..



For first condition please try this formula {=IF(A23="Saturday",IF(AND(A33<=TIMEVALUE("09:00:59"),A43>=TIMEVALUE("14:00:59")),"Present","Late Commer"),"Other Working Days")}

 

[uswyne]

single or multiple both can be useful

 

[uswyne]

single or multiple both can be useful

E F G H
Weekday First In Time Out Status
Thursday 7:15:22 15:45:45 Present
Friday 7:10:25 15:40:04 Present
Saturday 8:44:21 14:02:28 Early Going
Sunday Absent
Monday 7:20:15 15:38:14 Present


This what I use
=IF(F6="","Absent",IF(G6="","Absent",IF(G6<TIME(15,30,0),"Early Going",IF(E6="Saturday",(IF(F6>=TIME(9,0,59),"Late Coming","Present")),IF(E6="Saturday",(IF(G6<TIME(14,0,0),"Early Going")),(IF(F6>=TIME(7,50,59),"Late Coming","Present")))))))


but followings are not figuring out:


1. Saturday Off time 14:00, but in this formula i cant figured this out.
2. Sunday should be marked Off Day
3. or we can use compact formula instead of this