Forum Discussion
How can I count overall staffing time per hour per day?
So, i have my staff log in and log out time, but i need to show it as total mins worked per hour block.
The problem I am encountering is if someone finishes mid hour as i will get the value incorrect!!!
This is the formula i am using at present for the cell highlighted in red (CELL O8) - but it should read 42 mins, only 11
Thoughts?
=IF(AND($H8<P$2,$H8<O$2),"",IF(IF($H8<P$2,SUM($H8-$F8),IF($F8>O$3," ",SUM(O$3-$F8)-SUM($M8:N8)))<0," ",IF($H8<P$2,$H8-O$2,IF($F8>O$3," ",SUM(O$3-$F8)-SUM($M8:N8)))))
| Login | Date | Logout | Date | Duration | 01:00:00 | ||||||||||||||||
| 08:00:00 | 09:00:00 | 10:00:00 | 11:00:00 | 12:00:00 | 13:00:00 | 14:00:00 | 15:00:00 | 16:00:00 | 17:00:00 | 18:00:00 | 19:00:00 | 20:00:00 | 21:00:00 | 22:00:00 | 23:00:00 | ||||||
| 09:00:00 | 10:00:00 | 11:00:00 | 12:00:00 | 13:00:00 | 14:00:00 | 15:00:00 | 16:00:00 | 17:00:00 | 18:00:00 | 19:00:00 | 20:00:00 | 21:00:00 | 22:00:00 | 23:00:00 | |||||||
| 01/07/2022 | 12:15:14 | 01/07/2022 | 15:10:28 | 2:55:14 | 00:44:46 | 01:00:00 | 01:00:00 | 00:10:28 | |||||||||||||
| 01/07/2022 | 15:16:27 | 01/07/2022 | 21:21:58 | 6:05:31 | 00:43:33 | 01:00:00 | 01:00:00 | 01:00:00 | 01:00:00 | 01:00:00 | 00:21:58 | ||||||||||
| 03/07/2022 | 10:00:23 | 03/07/2022 | 17:10:48 | 7:10:25 | 00:59:37 | 01:00:00 | 01:00:00 | 01:00:00 | 01:00:00 | 01:00:00 | 01:00:00 | 00:10:48 | |||||||||
| 03/07/2022 | 17:17:58 | 03/07/2022 | 19:00:27 | 1:42:29 | 00:42:02 | 01:00:00 | 00:00:27 | ||||||||||||||
| 05/07/2022 | 10:30:56 | 05/07/2022 | 10:42:08 | 11:12 | 00:42:08 | ||||||||||||||||
| 05/07/2022 | 10:42:11 | 05/07/2022 | 10:42:55 | 0:44 | |||||||||||||||||
| 05/07/2022 | 10:46:52 | 05/07/2022 | 20:20:09 | 9:33:17 | |||||||||||||||||
| 05/07/2022 | 20:31:33 | 05/07/2022 | 21:02:25 | 30:52 | |||||||||||||||||
| 06/07/2022 | 12:15:22 | 06/07/2022 | 21:15:05 | 8:59:43 | |||||||||||||||||
| 07/07/2022 | 12:15:25 | 07/07/2022 | 16:52:30 | 4:37:05 | |||||||||||||||||
| 07/07/2022 | 16:52:56 | 07/07/2022 | 17:31:53 | 38:57 | |||||||||||||||||
| 08/07/2022 | 12:15:53 | 08/07/2022 | 21:16:12 | 9:00:19 | |||||||||||||||||
| 10/07/2022 | 10:00:07 | 10/07/2022 | 16:02:40 | 6:02:33 | |||||||||||||||||
| 11/07/2022 | 13:00:38 | 11/07/2022 | 20:11:06 | 7:10:28 | |||||||||||||||||
| 13/07/2022 | 12:15:12 | 13/07/2022 | 21:44:35 | 9:29:23 | |||||||||||||||||
| 14/07/2022 | 12:15:25 | 14/07/2022 | 21:15:12 | 8:59:47 |
Hi Rwogan
You can achieve your desired results by using the below formula.
=IF(AND(MATCH($B4,$F$2:$U$2,1)=MATCH($D4,$F$2:$U$2,1),MATCH($D4,$F$2:$U$2,1)=F$1),($D4-$B4),IF(MATCH($B4,$F$2:$U$2,1)=F$1,(F$3-$B4),IF(MATCH($D4,$F$2:$U$2,1)=F$1,($D4-F$2),IF(AND(MATCH($B4,$F$2:$U$2,1)<=F$1,MATCH($D4,$F$2:$U$2,1)>=F$1),TIME(1,0,0),""))))Please refer to the attached sample file wherein I have updated this formula for your reference.
I have observed some inconsistency in column E (duration). For instance, in row 11, the actual duration should be 30 minutes and 52 seconds, but it appears as 30 hours and 52 minutes. Anyhow, I have also put a formula for this column.
I hope it will be helpful.
Please let me know if it works for you.
Thanks
Tauqeer
