VBA Separate Day and Time to Another Column if condition meet

S

SamKhem

Board Regular
Joined
Mar 18, 2024
Messages
55
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
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
kvsrinivasamurthy said:
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
Click to expand...
Thank kvsrinivasamurthy, I still got error.

​

 
Upvote 0
kvsrinivasamurthy said:
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
Click to expand...
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
SamKhem said:
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.
Click to expand...
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
Fuji said:
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
Click to expand...
I mean if I have 30 days, so I need to separate 3 columns pers day (Amount, Date, Time).
Condition:
1-01/01/2024 at early 4:00:00 PM move column A,B,C to column D,E,F,
2-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
3-02/01/2024 later 4:00:00 PM to 03/01/2024 early 4:00:00 PM move column A,B,C to column J,K,L
4-.....................
5-.....................
6-....................
.
.
.
29-29/01/2024 later 4:00:00 PM to 30/01/2024 early 4:00:00 PM move column A,B,C to column bla bla bla.....
30-30/01/2024 later 4:00:00 PM
Until end of row
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Calculate hour total for each week
2
Replies
16
Views
234
CaptainGravyBum
C
D
  • Question Question
VBA code to transpose data from vertical to horizontal based on certain conditions.
Replies
2
Views
99
dsubash
D
P
  • Solved
Count all Dates prior to Row Date
Replies
3
Views
39
Fluff
Fluff
D
  • Solved
VBA to generate report
2
Replies
15
Views
274
dsubash
D
V
  • Solved
Arrays in VBA - error 13 type mismatch
2
Replies
18
Views
643
Alex Blakenburg
A

Forum statistics

Threads
1,226,516
Messages
6,191,496
Members
453,659
Latest member
thomji1

Share this page

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