timestamp listing to show more than >19hrs away per day?

B

Boboka

New Member
Joined
Jan 19, 2022
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hi, this is the timestamp listing for employees clocking in and out per month. Is there anyway to show any of the employees exceed more than 19hours, if yes, highlight it. As you can see from the listing sheet, some of the date may have many outs and ins on a same day. I would just like to have the latest time of clock in and out. Not sure whether excel formula can achieve this or VBA. thanks in advance.


Employee NameTimeStampStatus
Ali1/11/2024 5:53OUT
Ali1/11/2024 21:01IN
Ali2/11/2024 6:15OUT
Ali2/11/2024 18:54IN
Ali3/11/2024 11:53OUT
Ali3/11/2024 11:53OUT
Ali3/11/2024 13:56IN
Ali3/11/2024 15:42OUT
Ali3/11/2024 16:20IN
Ali3/11/2024 19:08OUT
Ali3/11/2024 20:16IN
Ali4/11/2024 5:53OUT
Ali4/11/2024 20:43IN
Ali5/11/2024 5:53OUT
Ali5/11/2024 20:45IN
Ali6/11/2024 5:56OUT
Ali6/11/2024 20:43IN
Ali7/11/2024 5:53OUT
Ali7/11/2024 20:42IN
Ali8/11/2024 5:55OUT
Ali8/11/2024 21:22IN
Ali9/11/2024 6:13OUT
Ali9/11/2024 19:15IN
Ali9/11/2024 20:51OUT
Ali9/11/2024 21:47IN
Ali9/11/2024 21:47IN
Ali9/11/2024 22:33OUT
Ali9/11/2024 22:42IN
Ali10/11/2024 12:04OUT
Ali10/11/2024 19:06IN
Ali10/11/2024 19:27OUT
Ali10/11/2024 19:51IN
Ali11/11/2024 5:52OUT
Ali11/11/2024 20:51IN
Ali12/11/2024 5:56OUT
Ali12/11/2024 20:49IN
Ali12/11/2024 22:29OUT
Ali12/11/2024 23:15IN
Ali13/11/2024 5:52OUT
Ali13/11/2024 20:54IN
Ali14/11/2024 5:54OUT
Ali14/11/2024 20:54IN
Ali15/11/2024 5:56OUT
Ali15/11/2024 20:46IN
Ali16/11/2024 5:59OUT
Ali16/11/2024 19:09IN
Ali17/11/2024 16:26OUT
Ali17/11/2024 17:26IN
Ali17/11/2024 19:51OUT
Ali17/11/2024 20:56IN
Ali18/11/2024 5:55OUT
Ali18/11/2024 20:49IN
Ali19/11/2024 5:57OUT
Ali19/11/2024 20:51IN
Ali20/11/2024 5:55OUT
Ali20/11/2024 21:18IN
Ali21/11/2024 5:55OUT
Ali21/11/2024 21:05IN
Ali22/11/2024 5:55OUT
Ali22/11/2024 21:06IN
Ali23/11/2024 6:08OUT
Ali23/11/2024 20:47IN
Ali23/11/2024 22:35OUT
Ali23/11/2024 23:28IN
Ali24/11/2024 16:37OUT
Ali24/11/2024 17:38IN
Ali24/11/2024 20:07OUT
Ali24/11/2024 20:35IN
Ali24/11/2024 20:47OUT
Ali24/11/2024 20:53IN
Ali24/11/2024 21:30OUT
Ali24/11/2024 23:05IN
Ali25/11/2024 5:55OUT
Ali25/11/2024 20:52IN
Ali26/11/2024 5:54OUT
Ali26/11/2024 20:44IN
Ali27/11/2024 5:55OUT
Ali27/11/2024 20:45IN
Ali28/11/2024 5:51OUT
Ali28/11/2024 20:42IN
Ali28/11/2024 22:29OUT
Ali28/11/2024 22:59IN
Ali29/11/2024 5:56OUT
Ali29/11/2024 20:49IN
Ali30/11/2024 6:10OUT
Ali30/11/2024 20:48IN
Tom1/11/2024 7:06OUT
Tom1/11/2024 22:01IN
 

Attachments

  • Screenshot 2024-12-15 224136.png
    Screenshot 2024-12-15 224136.png
    54.3 KB · Views: 3

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
As you already have it in a table I'd add a column with Worked time calculation. I excluded from results situation wher there is OUT with no IN record before (bot Tom and Ali had such situations. In "non displaying result I used 0 , not "" because you can use numerical filters to show only situations with >19hrs (as for Nov 11th). But this calls for custom formatting of this extra column with standard time format and two commas ( first comma is for negative (anyway not allowed) and second comma for 0 values formatting)
See screenshot. In my case this is formatting with two semicolons, because semicolon, not comma is a separator in my local settings.

Book1
ABCD
1Employee NameTimeStampStatustime worked
2Ali01.11.2024 05:53OUT 
3Ali01.11.2024 21:01IN 
4Ali02.11.2024 06:15OUT09:14:00
5Ali02.11.2024 18:54IN 
6Ali03.11.2024 11:53OUT16:59:00
7Ali03.11.2024 11:53OUT 
8Ali03.11.2024 13:56IN 
9Ali03.11.2024 15:42OUT01:46:00
10Ali03.11.2024 16:20IN 
11Ali03.11.2024 19:08OUT02:48:00
12Ali03.11.2024 20:16IN 
13Ali04.11.2024 05:53OUT09:37:00
14Ali04.11.2024 20:43IN 
15Ali05.11.2024 05:53OUT09:10:00
16Ali05.11.2024 20:45IN 
17Ali06.11.2024 05:56OUT09:11:00
18Ali06.11.2024 20:43IN 
19Ali07.11.2024 05:53OUT09:10:00
20Ali07.11.2024 20:42IN 
21Ali08.11.2024 05:55OUT09:13:00
22Ali08.11.2024 21:22IN 
23Ali09.11.2024 06:13OUT08:51:00
24Ali09.11.2024 19:15IN 
25Ali09.11.2024 20:51OUT01:36:00
26Ali09.11.2024 21:47IN 
27Ali09.11.2024 21:47IN 
28Ali09.11.2024 22:33OUT00:46:00
29Ali09.11.2024 22:42IN 
30Ali10.11.2024 12:04OUT13:22:00
31Ali10.11.2024 19:06IN 
32Ali10.11.2024 19:27OUT00:21:00
33Ali10.11.2024 19:51IN 
34Ali11.11.2024 05:52OUT10:01:00
35Ali11.11.2024 20:51IN 
36Ali12.11.2024 05:56OUT09:05:00
37Ali12.11.2024 20:49IN 
38Ali12.11.2024 22:29OUT01:40:00
39Ali12.11.2024 23:15IN 
40Ali13.11.2024 05:52OUT06:37:00
41Ali13.11.2024 20:54IN 
42Ali14.11.2024 05:54OUT09:00:00
43Ali14.11.2024 20:54IN 
44Ali15.11.2024 05:56OUT09:02:00
45Ali15.11.2024 20:46IN 
46Ali16.11.2024 05:59OUT09:13:00
47Ali16.11.2024 19:09IN 
48Ali17.11.2024 16:26OUT21:17:00
49Ali17.11.2024 17:26IN 
50Ali17.11.2024 19:51OUT02:25:00
51Ali17.11.2024 20:56IN 
52Ali18.11.2024 05:55OUT08:59:00
53Ali18.11.2024 20:49IN 
54Ali19.11.2024 05:57OUT09:08:00
55Ali19.11.2024 20:51IN 
56Ali20.11.2024 05:55OUT09:04:00
57Ali20.11.2024 21:18IN 
58Ali21.11.2024 05:55OUT08:37:00
59Ali21.11.2024 21:05IN 
60Ali22.11.2024 05:55OUT08:50:00
61Ali22.11.2024 21:06IN 
62Ali23.11.2024 06:08OUT09:02:00
63Ali23.11.2024 20:47IN 
64Ali23.11.2024 22:35OUT01:48:00
65Ali23.11.2024 23:28IN 
66Ali24.11.2024 16:37OUT17:09:00
67Ali24.11.2024 17:38IN 
68Ali24.11.2024 20:07OUT02:29:00
69Ali24.11.2024 20:35IN 
70Ali24.11.2024 20:47OUT00:12:00
71Ali24.11.2024 20:53IN 
72Ali24.11.2024 21:30OUT00:37:00
73Ali24.11.2024 23:05IN 
74Ali25.11.2024 05:55OUT06:50:00
75Ali25.11.2024 20:52IN 
76Ali26.11.2024 05:54OUT09:02:00
77Ali26.11.2024 20:44IN 
78Ali27.11.2024 05:55OUT09:11:00
79Ali27.11.2024 20:45IN 
80Ali28.11.2024 05:51OUT09:06:00
81Ali28.11.2024 20:42IN 
82Ali28.11.2024 22:29OUT01:47:00
83Ali28.11.2024 22:59IN 
84Ali29.11.2024 05:56OUT06:57:00
85Ali29.11.2024 20:49IN 
86Ali30.11.2024 06:10OUT09:21:00
87Ali30.11.2024 20:48IN 
88Ian27.11.2024 20:45IN 
89Ian28.11.2024 05:51OUT09:06:00
90Ian28.11.2024 20:42IN 
91Ian28.11.2024 22:29OUT01:47:00
92Tom01.11.2024 07:06OUT 
93Tom01.11.2024 22:01IN 
Sheet1
Cell Formulas
RangeFormula
D2:D93D2=IF(OR([@Status]="IN",AND([@TimeStamp]=B1,[@Status]=C1,[@[Employee Name]]=A1)),0,IFERROR([@TimeStamp]-MAX(FILTER([TimeStamp],([Employee Name]=[@[Employee Name]])*([TimeStamp]<[@TimeStamp])*([Status]="IN"))),0))
 
Upvote 0
Hi, Kasper. Thank you so much for assisting.

I may have put out my question wrongly. As I saw from the result, you have 9 hrs 14 min for cell D4 meaning that you took IN (21:04hrs on 1/11/2024) - OUT(6:15hrs on 2/11/2024)


Actually, for each day, there should be one clock-out in cell B2 on 1/11/2024 at 05:53 hrs and one clock-in in cell B3 on 1/11/2024 at 21:01 hrs. This should have a difference of 15:08 hrs.


At Cell B23, this employee clocked out numerous times on 9/11/2024 at 06:13 hrs. The latest clock in numerous 9/11/2024 in cell B29 is at 22:42 hrs. This should have a difference of 16:29.


Each day should only have 1 output hour and min per day. I am not sure whether it can still be achieved?

Once again, thank you.

Employee NameTimeStampStatusTime Worked
Ali1/11/2024 5:53OUT15:08
Ali1/11/2024 21:01IN
Ali2/11/2024 6:15OUT12:39
Ali2/11/2024 18:54IN
Ali3/11/2024 11:53OUT8:23
Ali3/11/2024 11:53OUT
Ali3/11/2024 13:56IN
Ali3/11/2024 15:42OUT
Ali3/11/2024 16:20IN
Ali3/11/2024 19:08OUT
Ali3/11/2024 20:16IN
Ali4/11/2024 5:53OUT14:50
Ali4/11/2024 20:43IN
Ali5/11/2024 5:53OUT14:52
Ali5/11/2024 20:45IN
Ali6/11/2024 5:56OUT14:47
Ali6/11/2024 20:43IN
Ali7/11/2024 5:53OUT14:49
Ali7/11/2024 20:42IN
Ali8/11/2024 5:55OUT15:27
Ali8/11/2024 21:22IN
Ali9/11/2024 6:13OUT16:29
Ali9/11/2024 19:15IN
Ali9/11/2024 20:51OUT
Ali9/11/2024 21:47IN
Ali9/11/2024 21:47IN
Ali9/11/2024 22:33OUT
Ali9/11/2024 22:42IN
Ali10/11/2024 12:04OUT7:47
Ali10/11/2024 19:06IN
Ali10/11/2024 19:27OUT
Ali10/11/2024 19:51IN
 
Upvote 0
OK, I've been confused by IN and OUT meaning.
And it was not clear for me that for each day you can have many INs and OUts, but shall look only for earliest and latest (ignoring possible breaks like for instance Ali case on Nov 3rd, etc.).
try:
Excel Formula: 
=IF(INT([@TimeStamp])<>IFERROR(INT(B1),0),MAX(FILTER([TimeStamp],([Employee Name]=[@[Employee Name]])*(INT([TimeStamp])=INT([@TimeStamp]))))-[@TimeStamp],"")




1267829 rejestrator wejść i wyjść.xlsx
ABCD
1Employee NameTimeStampStatustime worked
2Ali01.11.2024 05:53OUT15:08:00
3Ali01.11.2024 21:01IN 
4Ali02.11.2024 06:15OUT12:39:00
5Ali02.11.2024 18:54IN 
6Ali03.11.2024 11:53OUT08:23:00
7Ali03.11.2024 11:53OUT 
8Ali03.11.2024 13:56IN 
9Ali03.11.2024 15:42OUT 
10Ali03.11.2024 16:20IN 
11Ali03.11.2024 19:08OUT 
12Ali03.11.2024 20:16IN 
13Ali04.11.2024 05:53OUT14:50:00
14Ali04.11.2024 20:43IN 
15Ali05.11.2024 05:53OUT14:52:00
16Ali05.11.2024 20:45IN 
17Ali06.11.2024 05:56OUT14:47:00
18Ali06.11.2024 20:43IN 
19Ali07.11.2024 05:53OUT14:49:00
20Ali07.11.2024 20:42IN 
21Ali08.11.2024 05:55OUT15:27:00
22Ali08.11.2024 21:22IN 
23Ali09.11.2024 06:13OUT16:29:00
24Ali09.11.2024 19:15IN 
25Ali09.11.2024 20:51OUT 
26Ali09.11.2024 21:47IN 
27Ali09.11.2024 21:47IN 
28Ali09.11.2024 22:33OUT 
29Ali09.11.2024 22:42IN 
30Ali10.11.2024 12:04OUT07:47:00
31Ali10.11.2024 19:06IN 
32Ali10.11.2024 19:27OUT 
33Ali10.11.2024 19:51IN 
34Ali11.11.2024 05:52OUT14:59:00
35Ali11.11.2024 20:51IN 
36Ali12.11.2024 05:56OUT17:19:00
37Ali12.11.2024 20:49IN 
38Ali12.11.2024 22:29OUT 
39Ali12.11.2024 23:15IN 
40Ali13.11.2024 05:52OUT15:02:00
41Ali13.11.2024 20:54IN 
42Ali14.11.2024 05:54OUT15:00:00
43Ali14.11.2024 20:54IN 
44Ali15.11.2024 05:56OUT14:50:00
45Ali15.11.2024 20:46IN 
46Ali16.11.2024 05:59OUT13:10:00
47Ali16.11.2024 19:09IN 
48Ali17.11.2024 16:26OUT04:30:00
49Ali17.11.2024 17:26IN 
50Ali17.11.2024 19:51OUT 
51Ali17.11.2024 20:56IN 
52Ali18.11.2024 05:55OUT14:54:00
53Ali18.11.2024 20:49IN 
54Ali19.11.2024 05:57OUT14:54:00
55Ali19.11.2024 20:51IN 
56Ali20.11.2024 05:55OUT15:23:00
57Ali20.11.2024 21:18IN 
58Ali21.11.2024 05:55OUT15:10:00
59Ali21.11.2024 21:05IN 
60Ali22.11.2024 05:55OUT15:11:00
61Ali22.11.2024 21:06IN 
62Ali23.11.2024 06:08OUT17:20:00
63Ali23.11.2024 20:47IN 
64Ali23.11.2024 22:35OUT 
65Ali23.11.2024 23:28IN 
66Ali24.11.2024 16:37OUT06:28:00
67Ali24.11.2024 17:38IN 
68Ali24.11.2024 20:07OUT 
69Ali24.11.2024 20:35IN 
70Ali24.11.2024 20:47OUT 
71Ali24.11.2024 20:53IN 
72Ali24.11.2024 21:30OUT 
73Ali24.11.2024 23:05IN 
74Ali25.11.2024 05:55OUT14:57:00
75Ali25.11.2024 20:52IN 
76Ali26.11.2024 05:54OUT14:50:00
77Ali26.11.2024 20:44IN 
78Ali27.11.2024 05:55OUT14:50:00
79Ali27.11.2024 20:45IN 
80Ali28.11.2024 05:51OUT17:08:00
81Ali28.11.2024 20:42IN 
82Ali28.11.2024 22:29OUT 
83Ali28.11.2024 22:59IN 
84Ali29.11.2024 05:56OUT14:53:00
85Ali29.11.2024 20:49IN 
86Ali30.11.2024 06:10OUT14:38:00
87Ali30.11.2024 20:48IN 
88Tom01.11.2024 07:06OUT14:55:00
89Tom01.11.2024 22:01IN 
Sheet1 (2)
Cell Formulas
RangeFormula
D2:D89D2=IF(INT([@TimeStamp])<>IFERROR(INT(B1),0),MAX(FILTER([TimeStamp],([Employee Name]=[@[Employee Name]])*(INT([TimeStamp])=INT([@TimeStamp]))))-[@TimeStamp],"")
 
Upvote 0
BTW. If such method is employed, in sample data there is no 19hrs+ situation.
BTW2. If you want to use number filters do the same trick as before - use
,0)
instead of
,"")
at the end of formula
and custom formatting of cells with time and two commas, so for instance:
hh:mm:ss,,

Otherwise "" is treated as a text (quite obvious) and number filters are not available in time worked column.
 
Upvote 0
Solution
Hi Kaper.

This performs extremely nicely.
I appreciate you saving me the time. I always did it by hand manually for every new month prior to this. I greatly appreciate it. Thank you so much. ☺️
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Calculate start time from end time with multiple times per day for each ID
2
Replies
10
Views
313
Fluff
Fluff
john Smith72
  • Solved
count days between dates for each month by drop list
Replies
9
Views
380
Tetra201
Tetra201
J
  • Question Question
conditional formatting the most recent day of the month in a list
Replies
1
Views
214
awoohaw
awoohaw
O
  • Solved
Travel Duration allowing for more than one day and different time zones
Replies
7
Views
151
myall_blues
myall_blues
K
  • Solved
Automate a table showing the total number of times the numbers are less than 18 and greater than 18 for each time per day
Replies
5
Views
376
keranali
K

Forum statistics

Threads
1,224,752
Messages
6,180,743
Members
452,996
Latest member
nelsonsix66

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top