Time calculation for attendance

[arvindarvind24]

Hi All, Need your help on Time calculation for attendance.

Date	EmpCode	Start Time	End Time	Worked Time	IN Time	Full / Not Full Day	IN_Time But Not Full Day
03-10-2022	3698	09:11:39	17:40:09	08:28:30	In_time	Not Full Day	No
06-10-2022	3698	09:22:53	18:27:47	09:04:54	In_time	Full Day	Yes
07-10-2022	3698	09:18:13	18:23:44	09:05:31	In_time	Full Day	Yes
10-10-2022	3698	09:25:31	18:27:17	09:01:46	In_time	Full Day	Yes
17-10-2022	3698	09:18:03	18:22:29	09:04:26	In_time	Full Day	Yes
18-10-2022	3698	11:08:14	18:08:03	06:59:49	Not_In_Time	Not Full Day	No
12-10-2022	4242	10:29:31	19:32:20	09:02:49	In_time	Full Day	Yes
13-10-2022	4242	10:34:02	19:33:50	08:59:48	Not_In_Time	Not Full Day	No
14-10-2022	4242	10:32:58	15:09:51	04:36:53	Not_In_Time	Not Full Day	No
17-10-2022	4242	10:46:10	20:34:43	09:48:33	Not_In_Time	Full Day	No
18-10-2022	4242	10:29:41	20:19:36	09:49:55	In_time	Full Day	Yes
19-10-2022	4242	10:27:07	19:50:32	09:23:25	In_time	Full Day	Yes
20-10-2022	4242	11:05:52	17:27:58	06:22:06	Not_In_Time	Not Full Day	No
21-10-2022	4242	11:03:11	20:52:28	09:49:17	Not_In_Time	Full Day	No

Details

IN Time	If Start the is less the equal to 10:30 the In_time else Not_In_Time
Full / Not Full Day	if worked 9 hours is completed the its full day else Not Full Day
IN_Time But Not Full Day	Emp is in tine but not completede full day

https://drive.google.com/file/d/14H5Y2lVx_OoKSTAgWhUPGSi1dRbMYjaz/view?usp=sharing 

@amitchauhan @bvanderwatt @ray_ux @tamerj1 @SpartaBI @v-chenyue-msft 

[arvindarvind24]

@mangaus1111 Thank you for your replay and its working fine, But when we use the min in the  IN_Time its not working.

IN Time =

SWITCH(TRUE(),

HOUR([Start Time])< 10:30,"IN Time",

"Not in Time"

)

[mangaus1111]

Hi @arvindarvind24 ,

sorry I supposed it was < 10 , but if you need < 10:30, then you have to use this formula:

In Time =

SWITCH(TRUE(),

[Start Time]<= 1.4375,"In Time",

"Not in Time"

)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[arvindarvind24]

@mangaus1111 still not able to get the value.

[mangaus1111 · Accepted solution]

Hi @arvindarvind24 ,

try these 3 measures

IN Time =

SWITCH(TRUE(),

HOUR([Start Time])< 10,"IN Time",

"Not in Time"

)

Full / Not Full Day =

SWITCH(TRUE(),

       HOUR([Worked Time])<9,"Not Full",

       "Full"

)

IN_Time But Not Full Day =

SWITCH(TRUE(),

       [IN Time] = "IN Time" && [Full / Not Full Day] = "Full",

       "YES","NO"

)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.