Forum Discussion
Calculating Staff Working Hours
Hi, I'm trying to calculate Staff working hours by status ("I" means In and "O" means Out) and user ID (this data is coming from a biometric system)
and currently im using this calculation
and it gave me this
It does not give me the desired results, by right for 18/1/2020 for AM - it should be around 2.22 hours and PM should be around 4 hours (taking only 1:44pm and 5:44pm).
Please help me to solve this. Thank you in advance
Data for testing in .csv:
https://drive.google.com/drive/folders/1MUnxQDX55LhzcQ_rTpL7TBKMHxl0-pVC?usp=sharing
9 Replies
buzzkoi69 , This a new column will give you the time of I for O
if([checkType]="O", maxx(filter(Table, [checktime] =earlier([checktime]) && [checkType]="I"),[checktime]), blank())And you can use datediff to get time.
buzzkoi69 , please share data in table format.
Hi buzzkoi69,
You can try to use following calculate column formula to calculate the working hour on statues 'O' to get the working hour between the nearest 'in' and 'out' status based on 'user id' group:
Working Hour = IF ( Table[CHECKTYPE] = "O", DATEDIFF ( MAXX ( FILTER ( ALL ( Table ), [CHECKTIME] <= EARLIER ( Table[CHECKTIME] ) && [USERID] = EARLIER ( Table[USERID] ) && [CHECKTYPE] <> EARLIER ( Table[CHECKTYPE] ) ), [CHECKTIME] ), Table[CHECKTIME], SECOND ) )If this formula does not work on your side, share some dummy data to help us clarify your data structure and coding formula on it.
Regards,
Xiaoxin Sheng
Hi Anonymous, It's not working. I've shared the file through google drive, please refer to my original post. Thank you
HI buzzkoi69,
I test on the sample data and my formula works well, it can get the diff between previous 'in' and current 'out' working hours. (current it calculate the total second if you want you can modify the datediff function parameter to display the hours)
Regards,
XIaoxin Sheng
