VBA Separate Day and Time to Another Column if condition meet

SamKhem

Board Regular
Joined
Mar 18, 2024
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
Dear Senior member

I would like to ask about run vba by separate day and time if on 01/01/2024 at early 4:00:00 PM move column A,B,C to column D,E,F, if on 01/01/2024 later 4:00:00 PM to 02/01/2024 early 4:00:00 PM move column A,B,C to column G,H,I ...etc.

AmountDateTime
40001/01/20241:00:58 AM
30001/01/20242:13:52 AM
85001/01/20242:25:36 AM
100101/01/20243:21:48 AM
3001/01/202411:08:53 AM
5001/01/202411:25:06 AM
10001/01/202412:07:18 PM
20001/01/202412:11:48 PM
10001/01/202412:15:12 PM
50001/01/202412:15:54 PM
178.301/01/202412:15:55 PM
200001/01/202412:15:55 PM
140001/01/202412:19:12 PM
250001/01/20242:56:21 PM
10001/01/20243:05:04 PM
46.4901/01/20243:07:20 PM
3850.5701/01/20243:07:57 PM
1001/01/20243:16:21 PM
13401/01/20243:17:35 PM
6.1201/01/20243:26:24 PM
100001/01/20243:42:15 PM
100001/01/20243:43:26 PM
4501/01/20243:43:44 PM
6401/01/20243:44:33 PM
2.501/01/20243:56:23 PM
15001/01/20243:56:43 PM
50001/01/20244:01:46 PM
6501/01/20244:07:40 PM
5001/01/20244:10:40 PM
4001/01/20244:12:44 PM
15001/01/20244:15:02 PM
30001/01/20244:21:22 PM
11001/01/20244:36:56 PM
10001/01/20244:39:26 PM
700001/01/20244:39:59 PM
3901/01/20244:52:08 PM
2601/01/20245:03:02 PM
10001/01/20245:04:52 PM
151001/01/20245:05:45 PM
1201/01/20245:09:16 PM
6501/01/20245:11:10 PM
8.3701/01/20245:13:06 PM
30001/01/20245:17:00 PM
300001/01/20245:19:37 PM
244.201/01/20245:36:54 PM
13001/01/20245:41:35 PM
8001/01/20245:52:10 PM
50001/01/20246:00:30 PM
10001/01/20246:05:48 PM
24001/01/20246:16:36 PM
10001/01/20246:16:49 PM
7001/01/20246:18:05 PM
0.3401/01/20246:24:49 PM
1501/01/20246:26:19 PM
6001/01/20246:27:51 PM
2401/01/20246:31:39 PM
102.6601/01/20246:33:24 PM
20001/01/20246:35:32 PM
22001/01/20246:38:59 PM
50001/01/20246:39:34 PM
101/01/20246:40:55 PM
50001/01/20246:41:17 PM
91001/01/20246:58:30 PM
150001/01/20246:59:18 PM
1001/01/20247:03:48 PM
28001/01/20247:08:11 PM
1201/01/20247:12:08 PM
50001/01/20247:12:43 PM
100001/01/20247:13:57 PM
5001/01/20247:15:14 PM
501/01/20247:18:09 PM
100001/01/20247:20:06 PM
101/01/20247:25:59 PM
405501/01/20247:43:39 PM
124.9801/01/20247:43:55 PM
505001/01/20247:47:53 PM
15001/01/20247:49:49 PM
20001/01/20247:51:25 PM
20001/01/20247:51:57 PM
0.1101/01/20248:05:33 PM
60001/01/20248:11:45 PM
1101/01/20248:14:04 PM
1701/01/20248:18:47 PM
4001/01/20248:19:17 PM
20001/01/20248:20:59 PM
3001/01/20248:33:13 PM
6501/01/20248:35:23 PM
50501/01/20248:39:32 PM
13001/01/20248:40:01 PM
501/01/20248:46:05 PM
40001/01/20248:46:52 PM
25001/01/20248:50:01 PM
36601/01/20248:52:52 PM
225001/01/20248:54:00 PM
701/01/20248:59:39 PM
11.0201/01/20249:04:16 PM
201/01/20249:12:05 PM
3501/01/20249:20:30 PM
20001/01/20249:20:31 PM
13001/01/20249:22:57 PM
20001/01/20249:25:18 PM
25001/01/20249:27:23 PM
2101/01/20249:28:52 PM
12001/01/20249:29:21 PM
30001/01/20249:30:02 PM
1.1601/01/20249:30:06 PM
34.201/01/20249:35:53 PM
10001/01/20249:37:58 PM
20001/01/20249:38:44 PM
3001/01/20249:39:01 PM
15001/01/20249:49:23 PM
27.1701/01/20249:51:40 PM
12001/01/20249:52:30 PM
10001/01/20249:53:31 PM
93.5901/01/202410:02:36 PM
5002/01/20241:00:36 PM
10002/01/20241:00:41 PM
107802/01/20241:01:03 PM
7002/01/20241:01:08 PM
102/01/20241:01:51 PM
20002/01/20241:01:57 PM
6002/01/20241:02:10 PM
2002/01/20241:02:47 PM
5602/01/20241:02:52 PM
10002/01/20241:03:28 PM
2602/01/20241:03:33 PM
100002/01/20241:04:05 PM
2002/01/20241:05:25 PM
26502/01/20241:05:25 PM
25002/01/20241:05:28 PM
15.1902/01/20241:05:51 PM
25002/01/20241:05:58 PM
10002/01/20241:06:00 PM
161.5402/01/20241:06:02 PM
11002/01/20241:06:13 PM
50002/01/20241:06:26 PM
402/01/20241:50:33 PM
502/01/20241:52:06 PM
13002/01/20241:52:33 PM
24.0502/01/20241:53:03 PM
883.6302/01/20241:53:19 PM
50.102/01/20241:53:31 PM
1502/01/20241:54:42 PM
50002/01/20241:55:41 PM
402/01/20241:57:11 PM
10502/01/20241:58:21 PM
20002/01/20241:58:51 PM
5002/01/202410:00:00 AM
2902/01/202410:01:03 AM
30002/01/202410:01:05 AM
1002/01/202410:01:37 AM
5002/01/202410:02:50 AM
5.7702/01/202410:03:08 AM
36602/01/202410:03:09 AM
27002/01/202410:04:38 AM
100002/01/202410:06:38 PM
2302/01/202410:07:41 AM
10002/01/202410:08:59 PM
10002/01/202410:09:21 PM
49.8902/01/202410:09:35 AM
25002/01/202410:10:14 AM
25002/01/202410:10:44 AM
15002/01/202410:11:05 AM
15002/01/202410:11:50 AM
122.0302/01/202410:12:50 PM
39702/01/202411:10:58 PM
20002/01/202411:10:59 AM
502/01/202411:11:27 AM
10.7602/01/202411:11:32 AM
16002/01/202411:12:34 AM
23402/01/202411:12:50 AM
365.3702/01/202411:12:55 AM
5002/01/202411:15:01 AM
10002/01/202411:15:36 AM
55002/01/202411:15:46 AM
55002/01/202411:15:57 AM
494.702/01/202411:16:40 AM
0.3402/01/202411:17:55 AM
20002/01/202411:18:29 AM
3002/01/202411:18:52 AM
5002/01/202411:19:02 AM
11002/01/202411:19:45 AM
1002/01/202411:20:31 AM
2002/01/202411:20:47 AM
1.402/01/202411:20:57 AM
35002/01/202411:22:19 AM
2002/01/202411:23:14 AM
307.8302/01/202411:24:14 AM
651.6102/01/202411:24:25 AM
10002/01/202411:25:05 AM
1294.8902/01/202411:25:45 AM
10002/01/202411:25:46 AM
20.6502/01/202411:26:25 AM
4002/01/202411:27:21 AM
35.7502/01/202411:27:25 AM
11502/01/202411:27:53 AM
14702/01/202411:28:07 AM
1002/01/202411:28:31 PM
6002/01/202411:28:46 AM
3002/01/202411:29:07 PM
260.4402/01/202411:29:55 AM
62.8402/01/202411:29:57 AM
3902/01/202411:30:07 AM
70002/01/202411:30:23 PM
30002/01/202411:30:26 AM
10502/01/202411:31:06 AM
5002/01/202411:31:15 AM
15002/01/202411:31:59 AM
74002/01/202411:32:05 AM
155.2302/01/202411:32:25 AM
20402/01/202411:32:25 AM
7002/01/202411:32:43 AM
170002/01/202411:33:44 AM
25002/01/202411:33:51 AM
333.8702/01/202411:34:05 AM
41002/01/202411:34:44 AM
25002/01/202411:34:44 AM
25002/01/202411:35:32 AM
502/01/202411:36:12 AM
3502/01/202411:36:44 AM
0.1902/01/202411:36:55 AM
3363.4802/01/202411:37:20 AM
10002/01/202411:37:55 AM
125.3802/01/202411:37:57 AM
20.7702/01/202411:38:35 AM
22002/01/202411:38:36 AM
20002/01/202411:38:44 AM
25502/01/202411:39:08 AM
25502/01/202411:39:35 AM
5002/01/202411:41:27 AM
402/01/202411:42:38 AM
12602/01/202411:43:02 AM
9002/01/202411:43:14 AM
0.1202/01/202411:43:19 AM
1502/01/202411:43:21 AM
5002/01/202411:43:26 AM
0.102/01/202411:44:02 AM
1002/01/202411:44:39 AM
17.3702/01/202411:45:13 AM
0.1602/01/202411:45:14 AM
30002/01/202411:45:25 AM
21802/01/202411:45:34 AM
5002/01/202411:45:36 AM
7002/01/202411:46:02 AM
3002/01/202411:46:02 AM
9102/01/202411:46:40 AM
3.5902/01/202411:47:42 AM
30002/01/202411:47:42 AM
2432.4902/01/202411:48:03 AM
30002/01/202411:48:56 AM
15002/01/202411:49:14 AM
30002/01/202411:49:42 AM
50002/01/202411:50:07 AM
5002/01/202411:50:09 AM
5402/01/202411:50:22 AM
84.1302/01/202411:50:37 AM
7502/01/202411:50:49 AM
5002/01/202411:51:18 AM
31502/01/202411:51:20 AM
1500.3802/01/202411:51:55 AM
10002/01/202411:52:06 AM
8002/01/202411:52:24 AM
695.7802/01/202411:52:26 AM
9902/01/202411:53:07 AM
37.1102/01/202411:53:31 AM
50002/01/202411:53:47 AM
5502/01/202411:55:30 AM
3002/01/202411:56:21 AM
2002/01/202411:56:23 AM
6002/01/202411:56:42 AM
25102/01/202411:57:44 PM
1239.9902/01/202411:58:31 AM
30002/01/202411:59:02 AM
45002/01/202412:00:26 AM
25002/01/202412:00:52 PM
102/01/202412:01:14 PM
602/01/202412:01:29 PM
475.0102/01/202412:02:27 PM
15002/01/202412:02:29 PM
8502/01/202412:03:27 PM
44.5402/01/202412:04:05 PM
2002/01/202412:05:26 PM
7002/01/202412:05:28 PM
50002/01/202412:05:51 PM
25502/01/202412:05:55 PM
1502/01/202412:07:21 PM
25002/01/202412:07:40 PM
10502/01/202412:07:51 AM
8002/01/202412:07:53 PM
40102/01/202412:08:42 PM
10002/01/202412:09:02 PM
925.8502/01/202412:09:03 PM
3.6602/01/202412:09:23 PM
3002/01/202412:09:33 PM
16002/01/202412:09:39 PM
0.802/01/202412:09:55 PM
3002/01/202412:11:21 PM
1002/01/202412:11:54 PM
52002/01/202412:11:55 PM
402/01/202412:12:25 PM
25002/01/202412:13:01 PM
350002/01/202412:13:32 PM
602/01/202412:13:46 PM
9002/01/202412:13:58 PM
5102/01/202412:14:11 PM
149.6702/01/202412:14:18 PM
13002/01/202412:14:55 PM
10502/01/202412:15:35 PM
3002/01/202412:15:48 PM
102/01/202412:16:03 PM
52002/01/202412:16:05 PM
1502/01/20244:00:47 PM
0.0102/01/20244:01:00 PM
7002/01/20244:01:05 PM
39.6502/01/20244:01:23 PM
10602/01/20244:01:52 PM
15002/01/20244:02:48 PM
3802/01/20244:03:31 PM
0.102/01/20244:04:24 PM
25702/01/20248:17:22 AM
60002/01/20248:17:23 AM
802/01/20248:18:01 PM
2202/01/20248:19:11 AM
1002/01/20248:19:46 AM
1002/01/20248:19:55 AM
1002/01/20248:20:48 AM
8002/01/20248:20:59 AM
7502/01/20248:21:36 AM
402/01/20248:22:11 PM
502/01/20248:22:40 AM
2002/01/20248:23:25 AM
5902/01/20248:24:21 AM
30002/01/20248:25:56 AM
8.3502/01/20248:26:19 AM
57.6102/01/20248:26:23 PM
30002/01/20248:26:48 PM
15002/01/20248:27:05 AM
281.6702/01/20248:27:05 AM
4002/01/20248:27:26 AM
4002/01/20248:27:33 PM
502/01/20248:28:03 PM
10002/01/20248:29:02 PM
50002/01/20248:30:49 AM
97.702/01/20248:31:24 AM
68002/01/20248:31:34 PM
12002/01/20248:31:44 PM
502/01/20248:31:48 AM
400002/01/20248:32:00 PM
15002/01/20248:32:06 AM
2202/01/20248:32:36 PM
10002/01/20248:33:12 AM
1202/01/20248:33:28 AM
16802/01/20248:33:29 AM
219.9602/01/20248:33:47 PM
12002/01/20248:34:06 AM
20002/01/20248:34:50 AM
50002/01/20248:35:02 AM
50002/01/20248:35:28 AM
10502/01/20248:38:41 PM
50002/01/20248:39:10 AM
1702/01/20248:40:03 PM
40002/01/20248:40:19 AM
502/01/20248:40:55 AM
23502/01/20248:41:02 AM
12.2102/01/20248:41:36 PM
30002/01/20248:42:06 AM
17002/01/20248:42:09 AM
5002/01/20248:42:55 AM
300.3302/01/20248:43:07 AM
5002/01/20248:43:33 AM
6.4302/01/20248:43:50 AM
1202/01/20248:43:57 AM
14.8802/01/20248:44:17 PM
25502/01/20248:45:08 AM
130002/01/20248:45:23 PM
84.7902/01/20248:45:36 PM
84.7902/01/20248:46:02 PM
1.7502/01/20248:46:46 PM
5.1502/01/20248:46:52 AM
194.4602/01/20248:46:53 PM
5002/01/20248:46:57 PM
10002/01/20248:47:48 AM
20002/01/20248:48:39 AM
7002/01/20248:48:55 AM
72.4502/01/20248:49:46 AM
4002/01/20248:50:05 PM
0.5202/01/20248:51:03 AM
502/01/20248:51:54 AM
1502/01/20248:51:58 PM
3002/01/20248:53:24 AM
30002/01/20248:55:04 AM
300002/01/20248:55:07 AM
55002/01/20248:55:14 AM
45802/01/20248:55:25 AM
21002/01/20248:55:59 AM
15002/01/20248:57:10 AM
2002/01/20248:58:03 AM
10002/01/20248:58:46 PM
3002/01/20248:58:53 AM
602/01/20248:59:29 PM
2002/01/20249:00:00 AM
100002/01/20249:00:27 AM
200002/01/20249:00:52 AM
6002/01/20249:01:12 PM
5.2502/01/20249:01:14 PM
150002/01/20249:01:22 AM
27002/01/20249:01:36 PM
1.2402/01/20249:02:28 PM
1002/01/20249:02:38 AM
16.1702/01/20249:02:51 AM
25.2602/01/20249:02:54 PM
182002/01/20249:04:11 AM
10002/01/20249:04:18 AM
2002/01/20249:04:31 AM
7802/01/20249:05:10 PM
1502/01/20249:05:21 AM
6002/01/20249:05:37 PM
1556.2602/01/20249:06:10 PM
7.9502/01/20249:07:13 PM
11002/01/20249:08:46 AM
4.1602/01/20249:09:20 AM
10002/01/20249:09:41 AM
30002/01/20249:09:44 AM
30002/01/20249:09:50 AM
184.0102/01/20249:10:29 PM
32002/01/20249:11:15 AM
8003/01/20241:45:08 PM
7003/01/20241:45:16 PM
26303/01/20241:45:26 PM
6203/01/20241:45:38 PM
5003/01/20241:45:43 PM
200003/01/20241:45:54 PM
10503/01/20241:46:24 PM
100003/01/20241:46:39 PM
9003/01/20241:46:39 PM
10003/01/20241:47:31 PM
1503/01/20241:48:22 PM
30003/01/20241:48:28 PM
409.6903/01/20241:48:30 PM
15003/01/20241:48:38 PM
30003/01/20241:48:44 PM
5003/01/20241:49:15 PM
1003/01/20241:49:23 PM
5503/01/20241:49:29 PM
16.4903/01/20241:49:36 PM
10003/01/20241:49:57 PM
25003/01/20241:49:57 PM
1503/01/20241:50:47 PM
70003/01/20241:51:06 PM
2003/01/20241:51:22 PM
29003/01/20241:51:44 PM
10003/01/20241:52:01 PM
61.4203/01/20241:52:44 PM
2003/01/20241:53:15 PM
14503/01/20241:53:17 PM
4103/01/20241:53:36 PM
6503/01/20241:53:51 PM
10003/01/20241:54:10 PM
0.903/01/20241:54:19 PM
12003/01/20241:54:38 PM
100003/01/20241:54:45 PM
20003/01/20241:55:07 PM
2503/01/20241:55:31 PM
7403/01/20241:55:36 PM
6003/01/20241:55:38 PM
503/01/20241:55:58 PM
4003/01/20241:56:18 PM
14003/01/20241:56:48 PM
80003/01/20241:57:10 PM
73503/01/20241:58:11 PM
70803/01/20241:59:23 PM
6003/01/202410:00:37 AM
1303/01/202410:00:41 PM
12503/01/202410:01:13 AM
26.6803/01/202410:01:55 AM
10003/01/202410:02:21 AM
40003/01/202410:03:31 AM
5503/01/202410:03:35 PM
200003/01/202410:03:40 AM
5003/01/202410:04:01 AM
40003/01/202410:04:34 AM
10003/01/202410:04:42 PM
120003/01/202410:04:58 AM
6303/01/202410:05:22 AM
7003/01/202410:05:29 PM
5003/01/202410:05:53 AM
12703/01/202410:06:59 AM
20.103/01/202410:07:17 AM
3003/01/202410:07:18 AM
13003/01/202410:07:25 PM
10003/01/202410:07:35 AM
17003/01/202410:07:56 PM
25003/01/202410:08:35 AM
6403/01/202410:08:41 AM
5003/01/202410:08:45 AM
13003/01/202410:08:53 PM
2503/01/202410:09:04 AM
13003/01/202410:09:50 PM
13503/01/202410:10:12 AM
15003/01/202410:10:19 AM
3503/01/202410:10:20 AM
17.1303/01/202410:10:29 AM
15003/01/202410:10:41 AM
7003/01/202410:10:51 AM
25003/01/202410:10:55 AM
15003/01/202410:11:06 AM
40003/01/202410:12:24 PM
18003/01/202410:12:44 AM
2503/01/202410:12:58 AM
503/01/202410:13:09 AM
6003/01/202410:13:09 AM
256.4703/01/202410:13:26 AM
12503/01/202410:13:37 AM
237.8603/01/202410:14:07 PM
231.403/01/202410:15:46 PM
10003/01/202410:16:23 PM
64003/01/202410:17:18 AM
12503/01/202410:18:13 AM
20003/01/202410:18:23 AM
6.203/01/202410:19:02 AM
190003/01/202410:19:21 AM
10003/01/202410:20:25 AM
25003/01/202410:20:31 AM
40003/01/202410:22:14 AM
4003/01/202410:22:20 AM
1303/01/202410:22:38 AM
10003/01/202410:23:06 AM
6503/01/202410:23:15 AM
556.7803/01/202410:23:47 AM
30003/01/202410:24:14 AM
12003/01/202410:24:54 AM
1203/01/202410:25:37 AM
200003/01/202410:25:43 AM
703/01/202410:25:45 AM
12003/01/202410:25:45 AM
15.1303/01/202410:26:07 AM
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Try .
M1=start date, N1=Start Time, O1=End Date, P1=End Time
VBA Code:
Sub CopyData()
Dim A, Zm1, Zm2
Dim T&, Ro&, X&, Y&, Tm As Double, Tm1 As Double, Tm2 As Double

Tm1 = Range("M1") + Range("N1"): Tm2 = Range("O1") + Range("P1")
Zm1 = Format(Tm1, "MM/DD/YYYY h:mm AM/PM")
Zm2 = Format(Tm2, "MM/DD/YYYY h:mm AM/PM")

A = Range("A1").CurrentRegion.Offset(1, 0)
ReDim B(1 To UBound(A, 1) - 1, 1 To 3)
ReDim C(1 To UBound(A, 1) - 1, 1 To 3)
For T = 1 To UBound(A, 1) - 1
Tm = A(T, 2) + A(T, 3)
If Tm < Tm1 Then
X = X + 1
B(X, 1) = A(T, 1): B(X, 2) = A(T, 2): B(X, 3) = A(T, 3)
ElseIf Tm >= Tm1 And Tm < Tm2 Then
Y = Y + 1
C(Y, 1) = A(T, 1): C(Y, 2) = A(T, 2): C(Y, 3) = A(T, 3)
End If
Next T
Range(Range("D1"), Range("D1").End(xlDown)).Resize(, 3).Clear
If X > 0 Then
With Range("D1")
.CurrentRegion.Offset(1, 3).Resize(, 3).Clear
.Resize(1, 3) = Range("A1:C1").Value
.Offset(1, 0).Resize(X, 3) = B
.Offset(1, 1).Resize(X, 1).NumberFormat = Range("B2").NumberFormat
.Offset(1, 2).Resize(X, 1).NumberFormat = Range("C2").NumberFormat
End With
Else
MsgBox ("No values found Before " & Zm1)
End If

Range(Range("G1"), Range("G1").End(xlDown)).Resize(, 3).Clear
If Y > 0 Then
With Range("G1")
.Resize(1, 3) = Range("A1:C1").Value
.Offset(1, 0).Resize(Y, 3) = C
.Offset(1, 1).Resize(Y, 1).NumberFormat = Range("B2").NumberFormat
.Offset(1, 2).Resize(Y, 1).NumberFormat = Range("C2").NumberFormat
End With
Else
MsgBox ("No values found From " & Zm1 & " to " & Zm2)
End If

End Sub
 
Upvote 0
Try .
M1=start date, N1=Start Time, O1=End Date, P1=End Time
VBA Code:
Sub CopyData()
Dim A, Zm1, Zm2
Dim T&, Ro&, X&, Y&, Tm As Double, Tm1 As Double, Tm2 As Double

Tm1 = Range("M1") + Range("N1"): Tm2 = Range("O1") + Range("P1")
Zm1 = Format(Tm1, "MM/DD/YYYY h:mm AM/PM")
Zm2 = Format(Tm2, "MM/DD/YYYY h:mm AM/PM")

A = Range("A1").CurrentRegion.Offset(1, 0)
ReDim B(1 To UBound(A, 1) - 1, 1 To 3)
ReDim C(1 To UBound(A, 1) - 1, 1 To 3)
For T = 1 To UBound(A, 1) - 1
Tm = A(T, 2) + A(T, 3)
If Tm < Tm1 Then
X = X + 1
B(X, 1) = A(T, 1): B(X, 2) = A(T, 2): B(X, 3) = A(T, 3)
ElseIf Tm >= Tm1 And Tm < Tm2 Then
Y = Y + 1
C(Y, 1) = A(T, 1): C(Y, 2) = A(T, 2): C(Y, 3) = A(T, 3)
End If
Next T
Range(Range("D1"), Range("D1").End(xlDown)).Resize(, 3).Clear
If X > 0 Then
With Range("D1")
.CurrentRegion.Offset(1, 3).Resize(, 3).Clear
.Resize(1, 3) = Range("A1:C1").Value
.Offset(1, 0).Resize(X, 3) = B
.Offset(1, 1).Resize(X, 1).NumberFormat = Range("B2").NumberFormat
.Offset(1, 2).Resize(X, 1).NumberFormat = Range("C2").NumberFormat
End With
Else
MsgBox ("No values found Before " & Zm1)
End If

Range(Range("G1"), Range("G1").End(xlDown)).Resize(, 3).Clear
If Y > 0 Then
With Range("G1")
.Resize(1, 3) = Range("A1:C1").Value
.Offset(1, 0).Resize(Y, 3) = C
.Offset(1, 1).Resize(Y, 1).NumberFormat = Range("B2").NumberFormat
.Offset(1, 2).Resize(Y, 1).NumberFormat = Range("C2").NumberFormat
End With
Else
MsgBox ("No values found From " & Zm1 & " to " & Zm2)
End If

End Sub
Thank kvsrinivasamurthy, I still got error.

 
Upvote 0
Try .
M1=start date, N1=Start Time, O1=End Date, P1=End Time
VBA Code:
Sub CopyData()
Dim A, Zm1, Zm2
Dim T&, Ro&, X&, Y&, Tm As Double, Tm1 As Double, Tm2 As Double

Tm1 = Range("M1") + Range("N1"): Tm2 = Range("O1") + Range("P1")
Zm1 = Format(Tm1, "MM/DD/YYYY h:mm AM/PM")
Zm2 = Format(Tm2, "MM/DD/YYYY h:mm AM/PM")

A = Range("A1").CurrentRegion.Offset(1, 0)
ReDim B(1 To UBound(A, 1) - 1, 1 To 3)
ReDim C(1 To UBound(A, 1) - 1, 1 To 3)
For T = 1 To UBound(A, 1) - 1
Tm = A(T, 2) + A(T, 3)
If Tm < Tm1 Then
X = X + 1
B(X, 1) = A(T, 1): B(X, 2) = A(T, 2): B(X, 3) = A(T, 3)
ElseIf Tm >= Tm1 And Tm < Tm2 Then
Y = Y + 1
C(Y, 1) = A(T, 1): C(Y, 2) = A(T, 2): C(Y, 3) = A(T, 3)
End If
Next T
Range(Range("D1"), Range("D1").End(xlDown)).Resize(, 3).Clear
If X > 0 Then
With Range("D1")
.CurrentRegion.Offset(1, 3).Resize(, 3).Clear
.Resize(1, 3) = Range("A1:C1").Value
.Offset(1, 0).Resize(X, 3) = B
.Offset(1, 1).Resize(X, 1).NumberFormat = Range("B2").NumberFormat
.Offset(1, 2).Resize(X, 1).NumberFormat = Range("C2").NumberFormat
End With
Else
MsgBox ("No values found Before " & Zm1)
End If

Range(Range("G1"), Range("G1").End(xlDown)).Resize(, 3).Clear
If Y > 0 Then
With Range("G1")
.Resize(1, 3) = Range("A1:C1").Value
.Offset(1, 0).Resize(Y, 3) = C
.Offset(1, 1).Resize(Y, 1).NumberFormat = Range("B2").NumberFormat
.Offset(1, 2).Resize(Y, 1).NumberFormat = Range("C2").NumberFormat
End With
Else
MsgBox ("No values found From " & Zm1 & " to " & Zm2)
End If

End Sub
Thank kvsrinivasamurthy, I still got error.

 
Upvote 0
1739335281017.png

1739335281023.png


It show message error like this.
 
Upvote 0
It is not error. It is a message.
Where you have put the starting and ending date and time.
As per post #1
Start date is 1/1/24 Start time 4:00 Pm
End date is 2/1/24 End time 4:00 Pm.
These can be changed as required.
The cells where you have to put starting and ending Date and time is given in Post#2.
 
Upvote 0
I would like to ask about run vba by separate day and time if on 01/01/2024 at early 4:00:00 PM move column A,B,C to column D,E,F, if on 01/01/2024 later 4:00:00 PM to 02/01/2024 early 4:00:00 PM move column A,B,C to column G,H,I ...etc.
Code:
Sub test()
    With [a1].CurrentRegion.Resize(, 3)
        .Rows(1).Copy .Cells(1, 4).Resize(, 6)
        With .Offset(1, 3).Resize(, 3)
            .Formula = "=if($c2<=time(16,0,0),a2,"""")"
            .Value = .Value
            .NumberFormatLocal = Array("", "dd/m/yyyy", "h:mm:ss AM/PM")
        End With
        With .Offset(1, 6)
            .Formula = "=if($c2>time(16,0,0),a2,"""")"
            .Value = .Value
            .NumberFormatLocal = Array("", "dd/m/yyyy", "h:mm:ss AM/PM")
        End With
    End With
    Columns.AutoFit
End Sub
 
Upvote 0

Forum statistics

Threads
1,226,466
Messages
6,191,196
Members
453,646
Latest member
SteenP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top