Can you help me know your working hours and absences here?

[ezzat]

Date/Time	act h,m
26-Nov-24 8:06:51 AM
26-Nov-24 4:10:08 PM
27-Nov-24 8:05:32 AM
27-Nov-24 4:35:28 PM
28-Nov-24 7:02:54 AM
28-Nov-24 3:22:11 PM
30-Nov-24 2:55:01 PM
30-Nov-24 2:55:06 PM
01-Dec-24 7:06:19 AM
01-Dec-24 2:56:48 PM
02-Dec-24 2:59:50 PM
04-Dec-24 7:06:43 AM
04-Dec-24 2:57:57 PM
05-Dec-24 7:06:51 AM
05-Dec-24 2:49:09 PM
07-Dec-24 7:02:55 AM
07-Dec-24 2:57:15 PM
08-Dec-24 7:02:51 AM
08-Dec-24 3:04:49 PM
09-Dec-24 7:03:54 AM
09-Dec-24 3:08:36 PM

 

[Joe4]

We need more detail.
Please tell us your expected output from your example, and explain the logic in determining it.

 

[ezzat]

Output is the number of hours worked during the day

 

[Joe4]

Is the first time of each day the start time and the second time the end time?

 

[Joe4]

Assuming that is what you want to do, I see one potential issue with your data.
For 12/1/2024, you only have one record for that date, when I think you need two.

I assumed that every date will either have two entries (time-in/time-out) or no entries.
Under those assumptions, here is what I came up with:

So, in column D I listed all the days I want to return hours for. I entered my first date in cell D2, and then simply added one to each day under it, i.e. in D3:

=D2+1

and copied down for all rows.

Then, I entered this formula in cell E2 and copy down for all rows:

=IF(COUNTIFS(A$2:A$22,">="&D2,A$2:A$22,"<"&D2+1)<2,0,MAX(INDEX(A$2:A$22+(A$2:A$22>D2+1)*-1E+99, , ))-MIN(INDEX(A$2:A$22+(A$2:A$22<D2)*1E+99, , )))

I have to admit, I did not come up with the part of the formulas that calculates the MIN and MAX times for each date. I got it from this thread here:

How to get the earliest time each day and the last?

How do I get the first time in and the last time of each day out? Is it possible to use an if condition or macros? A 2018-08-17 12:03:29 2018-08-17 09:53:28 2018-08-16 18:49:31 2018-08-16 18:39:5...

stackoverflow.com

 

[ezzat]

Thanks A lot

 

[Joe4]

You are welcome.