How to extract Start and End time for a true/false event from a data sheet using vba code?

[Syed Ahmad Ali Shah]

Hi all,

I want to extract start and end time into separate columns from a data sheet based on shift timings as shown below;

Shift-1 timing = 06:31-14:30
Shift-2 timing = 14:31-22:30
Shift-3 timing = 22:31-06:30

Requirement:
Start Time (false condition) = ?
End Time (false condition) = ?

Date	Time	Event	Shift
24/02/2023	6:30	TRUE	1
24/02/2023	6:35	TRUE	1
24/02/2023	6:40	TRUE	1
24/02/2023	6:45	TRUE	1
24/02/2023	6:50	TRUE	1
24/02/2023	6:55	TRUE	1
24/02/2023	7:00	FALSE	1
24/02/2023	7:05	FALSE	1
24/02/2023	7:10	FALSE	1
24/02/2023	7:15	FALSE	1
24/02/2023	7:20	FALSE	1
24/02/2023	7:25	FALSE	1
24/02/2023	7:30	FALSE	1
24/02/2023	7:35	FALSE	1
24/02/2023	7:40	FALSE	1
24/02/2023	7:45	TRUE	1
24/02/2023	7:50	TRUE	1
24/02/2023	7:55	TRUE	1
24/02/2023	8:00	TRUE	1
24/02/2023	8:05	TRUE	1

 

[Syed Ahmad Ali Shah]

Thanks a lot for this so much guidance and support. Just guide me the last thing that how to automatically avail these entries in separate date wise files as shown below.

.. then it probably would have been a good idea to have explained that at the beginning and/or included an example like that.

23 03 12.xlsm
	A	B	C			F	G	H
1	Date	Time	Event			Start	End	Total
2	24/02/2023	6:30	TRUE			2023-02-24 07:00:00	2023-02-24 07:40:00	00:40
3	24/02/2023	6:35	TRUE			2023-02-24 07:50:00	2023-02-24 08:00:00	00:10
4	24/02/2023	6:40	TRUE
5	24/02/2023	6:45	TRUE
6	24/02/2023	6:50	TRUE
7	24/02/2023	6:55	TRUE
8	24/02/2023	7:00	FALSE
9	24/02/2023	7:05	FALSE
10	24/02/2023	7:10	FALSE
11	24/02/2023	7:15	FALSE
12	24/02/2023	7:20	FALSE
13	24/02/2023	7:25	FALSE
14	24/02/2023	7:30	FALSE
15	24/02/2023	7:35	FALSE
16	24/02/2023	7:40	FALSE
17	24/02/2023	7:45	TRUE
18	24/02/2023	7:50	FALSE
19	24/02/2023	7:55	FALSE
20	24/02/2023	8:00	FALSE
21	24/02/2023	8:05	TRUE
22
Start End

Cell Formulas
Range	Formula
F2:F5	F2	=IFERROR(AGGREGATE(15,6,(A$2:A$21+B$2:B$21)/((C$2:C$21=FALSE)*(C$1:C$20<>FALSE)),ROWS(F$1:F1)),"")
G2:G5	G2	=IFERROR(AGGREGATE(15,6,(A$2:A$21+B$2:B$21)/((C$2:C$21=FALSE)*(C$3:C$22<>FALSE)),ROWS(F$1:F1)),"")
H2:H5	H2	=IF(G2="","",G2-F2)

 

[Syed Ahmad Ali Shah]

Thanks a lot for this so much guidance and support. Just guide me the last thing that how to automatically avail these entries in separate date wise files as shown below.

View attachment 87392

Any response from anyone as its been a while that I posted this thread??