Start and End

[Muhammad Hussaan]

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

Hello,
My problem is similar but with few changes.I need help for the below task i am doing manually, and want to change it to excel macro/vba.
I have data in "Sheet 1" data in two columns D&E range D5:E4324, in D column there is date and time and in column E there is true and false; true means machine is running & false means machine is not running. The data is auto generated and keeps history of last 14 days.
The same file has sheets 1,2,3,4,5 upto 31 for the days of month. Current month on each sheet is available in cell M2 as (1,2,3... depending on the month)
Each sheet has portions of shift 1, shift 2 and shift 3
Normally we have maximum of six stoppages in a shift so i have made the sheets accordingly
Column B is start time Column C is end time
for shift 1 range is B9:C14
for shift 2 range is B16:C21
for shift 3 range is B23:C28
Each day has three shifts (Production Day start at 6:31 and end 6:30 date of start and end will be different)
Shift 1 Start 6:31 Shift 1 end 14:30
Shift 2 start 14:31 Shift 2 end 22:30
Shift 3 start 22:31 Shift 3 end 6:30
Objective is to record the start and stop time "False" whenever false start and ends, and place the start and end time on the basis of shift1, Shift 2, and Shift 3 and day sheet number and look month also.
For example in below data the data will go to sheet 24 and shift1 first event start of machine stop false is 8:10 and end at 9:00 and should not overwrite the data of sheet that is not available
As data range D5:E4324; Below is the sample data for understanding
2023-02-24 06:30:00
TRUE
2023-02-24 06:35:00
TRUE
2023-02-24 06:40:00
TRUE
2023-02-24 06:45:00
TRUE
2023-02-24 06:50:00
TRUE
2023-02-24 06:55:00
TRUE
2023-02-24 07:00:00
TRUE
2023-02-24 07:05:00
TRUE
2023-02-24 07:10:00
TRUE
2023-02-24 07:15:00
TRUE
2023-02-24 07:20:00
TRUE
2023-02-24 07:25:00
TRUE
2023-02-24 07:30:00
TRUE
2023-02-24 07:35:00
TRUE
2023-02-24 07:40:00
TRUE
2023-02-24 07:45:00
TRUE
2023-02-24 07:50:00
TRUE
2023-02-24 07:55:00
TRUE
2023-02-24 08:00:00
TRUE
2023-02-24 08:05:00
TRUE
2023-02-24 08:10:00
FALSE
2023-02-24 08:15:00
FALSE
2023-02-24 08:20:00
FALSE
2023-02-24 08:25:00
FALSE
2023-02-24 08:30:00
FALSE
2023-02-24 08:35:00
FALSE
2023-02-24 08:40:00
FALSE
2023-02-24 08:45:00
FALSE
2023-02-24 08:50:00
FALSE
2023-02-24 08:55:00
FALSE
2023-02-24 09:00:00
FALSE
2023-02-24 09:05:00
TRUE
2023-02-24 09:10:00
TRUE
2023-02-24 09:15:00
TRUE
2023-02-24 09:20:00
TRUE
2023-02-24 09:25:00
TRUE
2023-02-24 09:30:00
TRUE
2023-02-24 09:35:00
TRUE
2023-02-24 09:40:00
TRUE
2023-02-24 09:45:00
TRUE
2023-02-24 09:50:00
TRUE
2023-02-24 09:55:00
TRUE
2023-02-24 10:00:00
TRUE
2023-02-24 10:05:00
TRUE
2023-02-24 10:10:00
TRUE
2023-02-24 10:15:00
TRUE
2023-02-24 10:20:00
TRUE
2023-02-24 10:25:00
TRUE
2023-02-24 10:30:00
TRUE
2023-02-24 10:35:00
TRUE
2023-02-24 10:40:00
TRUE
2023-02-24 10:45:00
TRUE
2023-02-24 10:50:00
TRUE
2023-02-24 10:55:00
TRUE
2023-02-24 11:00:00
TRUE
2023-02-24 11:05:00
TRUE
2023-02-24 11:10:00
TRUE
2023-02-24 11:15:00
TRUE
2023-02-24 11:20:00
TRUE
2023-02-24 11:25:00
TRUE
2023-02-24 11:30:00
TRUE
2023-02-24 11:35:00
TRUE
2023-02-24 11:40:00
TRUE
2023-02-24 11:45:00
TRUE
2023-02-24 11:50:00
TRUE
2023-02-24 11:55:00
TRUE
2023-02-24 12:00:00
TRUE
2023-02-24 12:05:00
TRUE
2023-02-24 12:10:00
TRUE
2023-02-24 12:15:00
TRUE
2023-02-24 12:20:00
TRUE
2023-02-24 12:25:00
TRUE
2023-02-24 12:30:00
TRUE
2023-02-24 12:35:00
TRUE
2023-02-24 12:40:00
TRUE
2023-02-24 12:45:00
TRUE
2023-02-24 12:50:00
TRUE
2023-02-24 12:55:00
TRUE
2023-02-24 13:00:00
TRUE
2023-02-24 13:05:00
TRUE
2023-02-24 13:10:00
TRUE
2023-02-24 13:15:00
TRUE
2023-02-24 13:20:00
TRUE
2023-02-24 13:25:00
TRUE
2023-02-24 13:30:00
TRUE
2023-02-24 13:35:00
TRUE
2023-02-24 13:40:00
TRUE
2023-02-24 13:45:00
TRUE
2023-02-24 13:50:00
TRUE
2023-02-24 13:55:00
TRUE
2023-02-24 14:00:00
TRUE
2023-02-24 14:05:00
TRUE
2023-02-24

 

[Peter_SSs]

Duplicate to: Separate and transfer data in different sheet of same workbook based on criteria using macro

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.