Date & Time calculation

[Gina V]

I have 144K lines of data. My system logs a time stamp each time the employees switches to a new page. I need to add a start time at the beginning of each date and an end time at the end. EAch day could have over 7K lines of data and it's sequential, without any gaps, so I can't jump from one date to the next. Once I've added the start and end times, I need to add a calculation to pick up screen delays of more than 10 minutes.

 

[awoohaw]

Welcome to the forum!

To confirm, are you asking for a time difference from one row to the next?
If so, assuming your date/times (and they are date/times) are in column B starting in row 2. In cell C3, enter this formula and copy down:

=(A3-A2)*(60*24)>10

Then filter where that is TRUE.

 

[Gina V]

Not exactly....I have one column of dates (7K rows with the same date) and then another column with a time stamp. I need to know when the date changes from one to the next. So the first step would be to identify when the date in column A switches to the new day. Once that's done, I need to add a time at the beginning of each day (i.e. the EE's start time of 8:30) and then their end time at the end of each day. this would require the insertion of extra lines as well to accommodate the times.

 

[awoohaw]

then add the day and time columns together and put in the same formula.
assuming days are in column A and times are in column B.

=((A3+B3)-(A2+B2))*(60*24)>10

PS. I have no idea what you mean about "insertion" of lines. If the dates and times are on the same row, that makes no difference.
And, the difference in time from last thing in the afternoon to first thing in the morning will be a negative number, not greater than 10 minutes.
Or are you saying there is no timestamp when they clock in or clock out?
Of course, a visual example of a date change in a table or mini sheet using xl2bb add in (link below) would be helpful.

 

[Gina V]

I just meant that I would need to add a line at the beginning to insert a start time of 08:30:00 and a line at the end to include their end time of 16:30:00. Once done, I would need to add the calculation to display the gaps in time of > 10 mins. At the moment, I'm doing this all manually and then adding a conditional format to highlight gaps over 10 minutes. I'm not able/allowed to install the program to show you the data but have included a picture

DATE	TIME
2024-01-08	09:56:19
2024-01-08	09:56:19
2024-01-08	09:56:20
2024-01-08	09:56:21
2024-01-08	09:56:21
1,000 lines later….
2024-01-08	16:07:06
2024-01-08	16:07:07
2024-01-09	08:52:40
2024-01-09	08:52:41
2024-01-09	08:52:41
2024-01-09	08:52:41

 

[awoohaw]

then you can use a conditional statement, checking for a change in the day.

Book1
	A	B	C
1	DATE	TIME
2	2024-01-08	09:56:19
3	2024-01-08	09:56:19	FALSE
4	2024-01-08	09:56:20	FALSE
5	2024-01-08	09:56:21	FALSE
6	2024-01-08	09:56:21	FALSE
7	2024-01-08	16:07:06	TRUE
8	2024-01-08	16:07:07	FALSE
9	2024-01-09	08:52:40	TRUE
10	2024-01-09	08:52:41	FALSE
11	2024-01-09	08:52:41	FALSE
12	2024-01-09	08:52:41	FALSE
13	2024-01-10	16:25:00	TRUE
14	2024-01-11	08:36:00	FALSE
15	2024-01-12	11:52:41	TRUE
16	2024-01-13	12:52:41	TRUE
17	2024-01-14	13:52:41	TRUE
Sheet1

Cell Formulas
Range	Formula
C3:C17	C3	=((B3-IF(A3>A2,TIME(8,30,0),B2))*(24*60))>10

 

[Gina V]

Thanks foryour help! I will give it a try and see what happens!