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

 

[Legacy 143009]

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

That is a false condition? What does true mean?

Please share some desired end-result.

 

[jdellasala]

Even 2013 can handle this without VBA. Make a Shift table:

Book1
	N	O	P
4	Shift	Start	End
5	1	06:31	14:30
6	2	14:31	22:30
7	3	22:31	06:30
Sheet3

Then just VLOOKUP the shift:

Book1
	G	H	I	J	K	L
4	Date	Time	Event	Shift	Start	End
5	24/02/2023	6:30	TRUE	1	06:31	14:30
6	24/02/2023	6:35	TRUE	1	06:31	14:30
7	24/02/2023	6:40	TRUE	1	06:31	14:30
8	24/02/2023	6:45	TRUE	1	06:31	14:30
9	24/02/2023	6:50	TRUE	1	06:31	14:30
10	24/02/2023	6:55	TRUE	1	06:31	14:30
11	24/02/2023	7:00	FALSE	1	06:31	14:30
12	24/02/2023	7:05	FALSE	1	06:31	14:30
13	24/02/2023	7:10	FALSE	1	06:31	14:30
14	24/02/2023	7:15	FALSE	1	06:31	14:30
15	24/02/2023	7:20	FALSE	1	06:31	14:30
16	24/02/2023	7:25	FALSE	1	06:31	14:30
17	24/02/2023	7:30	FALSE	1	06:31	14:30
18	24/02/2023	7:35	FALSE	1	06:31	14:30
19	24/02/2023	7:40	FALSE	1	06:31	14:30
20	24/02/2023	7:45	TRUE	1	06:31	14:30
21	24/02/2023	7:50	TRUE	1	06:31	14:30
22	24/02/2023	7:55	TRUE	1	06:31	14:30
23	24/02/2023	8:00	TRUE	1	06:31	14:30
24	24/02/2023	8:05	TRUE	1	06:31	14:30
Sheet3

Cell Formulas
Range	Formula
K5:K24	K5	=VLOOKUP([@Shift],Shifts,2,0)
L5:L24	L5	=VLOOKUP([@Shift],Shifts,3,0)

That said, I don't think we understand what you really need to do.

 

[Syed Ahmad Ali Shah]

That is a false condition? What does true mean?

Please share some desired end-result.

In the mentioned table, False condition means that production stops. Similarly, true condition means production goes on continuously with normal conditions. I want to extract start and end time for false condition so that i be able to calculate my production time loss in a shift.

 

[Syed Ahmad Ali Shah]

Even 2013 can handle this without VBA. Make a Shift table:

Book1
	N	O	P
4	Shift	Start	End
5	1	06:31	14:30
6	2	14:31	22:30
7	3	22:31	06:30
Sheet3

Then just VLOOKUP the shift:

Book1
	G	H	I	J	K	L
4	Date	Time	Event	Shift	Start	End
5	24/02/2023	6:30	TRUE	1	06:31	14:30
6	24/02/2023	6:35	TRUE	1	06:31	14:30
7	24/02/2023	6:40	TRUE	1	06:31	14:30
8	24/02/2023	6:45	TRUE	1	06:31	14:30
9	24/02/2023	6:50	TRUE	1	06:31	14:30
10	24/02/2023	6:55	TRUE	1	06:31	14:30
11	24/02/2023	7:00	FALSE	1	06:31	14:30
12	24/02/2023	7:05	FALSE	1	06:31	14:30
13	24/02/2023	7:10	FALSE	1	06:31	14:30
14	24/02/2023	7:15	FALSE	1	06:31	14:30
15	24/02/2023	7:20	FALSE	1	06:31	14:30
16	24/02/2023	7:25	FALSE	1	06:31	14:30
17	24/02/2023	7:30	FALSE	1	06:31	14:30
18	24/02/2023	7:35	FALSE	1	06:31	14:30
19	24/02/2023	7:40	FALSE	1	06:31	14:30
20	24/02/2023	7:45	TRUE	1	06:31	14:30
21	24/02/2023	7:50	TRUE	1	06:31	14:30
22	24/02/2023	7:55	TRUE	1	06:31	14:30
23	24/02/2023	8:00	TRUE	1	06:31	14:30
24	24/02/2023	8:05	TRUE	1	06:31	14:30
Sheet3

Cell Formulas
Range	Formula
K5:K24	K5	=VLOOKUP([@Shift],Shifts,2,0)
L5:L24	L5	=VLOOKUP([@Shift],Shifts,3,0)

That said, I don't think we understand what you really need to do.

In the mentioned table, False condition means that production stops. Similarly, true condition means production goes on continuously with normal conditions. I want to extract start and end time for false condition so that i be able to calculate my production time loss in a shift.

 

[Peter_SSs]

What are your expected results for the sample data in post #1 & why?

 

[Syed Ahmad Ali Shah]

What are your expected results for the sample data in post #1 & why?

I want to note the time when false condition starts and ends. Also, the total time consumed against false condition.

 

[Peter_SSs]

Is this what you want then?

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
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	TRUE
19	24/02/2023	7:55	TRUE
20	24/02/2023	8:00	TRUE
21	24/02/2023	8:05	TRUE
Start End

Cell Formulas
Range	Formula
F2	F2	=AGGREGATE(15,6,(A2:A21+B2:B21)/NOT(C2:C21),1)
G2	G2	=AGGREGATE(14,6,(A2:A21+B2:B21)/NOT(C2:C21),1)
H2	H2	=G2-F2

 

[Syed Ahmad Ali Shah]

Is this what you want then?

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
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	TRUE
19	24/02/2023	7:55	TRUE
20	24/02/2023	8:00	TRUE
21	24/02/2023	8:05	TRUE
Start End

Cell Formulas
Range	Formula
F2	F2	=AGGREGATE(15,6,(A2:A21+B2:B21)/NOT(C2:C21),1)
G2	G2	=AGGREGATE(14,6,(A2:A21+B2:B21)/NOT(C2:C21),1)
H2	H2	=G2-F2

Yes, this helps me but if the false condition occurs twice or thrice in a shift or in a day (3 shifts in a day) then it would be a problem. What is the resolution for this problem? as i want separate entries for all the false events occurs in a day with the same pattern as you suggest for one event.

In below image, firstly false condition appears at 7:00-7:20 then again at 8:10-8:30. Now, how i got separate start and end time for these two events?

Kindly explain it according to the shifts as well (3-shifts in a day).

Shift-01 = 06:30-14:30
Shift-02 = 14:30-22:30
Shift-03 = 22:30-06:30

 

[Peter_SSs]

but if the false condition occurs twice or thrice in a shift or in a day (3 shifts in a day) then it would be a problem.

.. 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)