VBA Separate Day and Time to Another Column if condition meet

S

SamKhem

Board Regular
Joined
Mar 18, 2024
Messages
56
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
 
Sort by date Sort by votes
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
kvsrinivasamurthy said:
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.
Click to expand...

Fuji said:
Not sure if this works as you want.
Dates must be serial date, not text date...
Code: 
Sub test()
    Dim a, i&, ii&, iii&, t&, temp#
    With [a1].CurrentRegion.Resize(, 3)
        a = .Value2
        ReDim Preserve a(1 To UBound(a, 1), 1 To UBound(a, 2) * 100)
        For i = 2 To UBound(a, 1)
            temp = a(i, 2) + 1 + TimeSerial(16, 0, 0)
            ii = 0: t = t + 3
            If UBound(a, 2) < t + 3 Then ReDim Preserve a(1 To UBound(a, 1), 1 To UBound(a, 2) + 100)
            Do While temp >= a(i + ii, 2) + a(i + ii, 3)
                For iii = 1 To 3
                    a(i + ii, t + iii) = a(i + ii, iii)
                Next
                ii = ii + 1
                If i + ii > UBound(a, 1) Then Exit Do
            Loop
            i = i + ii - 1
        Next
        With .Resize(, t + 3)
            .Value = a
            .Rows(2).Resize(, 3).Copy
            .Columns(4).Resize(, t).PasteSpecial xlPasteFormats
            .Columns.AutoFit
        End With
        Application.CutCopyMode = False
        Application.Goto [a1]
    End With
End Sub
Click to expand...
Thank Fuji for feedback and let me try.
 
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
278
CaptainGravyBum
C
D
  • Question Question
VBA code to transpose data from vertical to horizontal based on certain conditions.
Replies
2
Views
120
dsubash
D
P
  • Solved
Count all Dates prior to Row Date
Replies
3
Views
52
Fluff
Fluff
D
  • Solved
VBA to generate report
2
Replies
15
Views
291
dsubash
D
V
  • Solved
Arrays in VBA - error 13 type mismatch
2
Replies
18
Views
653
Alex Blakenburg
A

Forum statistics

Threads
1,226,840
Messages
6,193,272
Members
453,787
Latest member
Dekowrage

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