Convert 30 min interval data to 5 min intervals

MAP77

Board Regular
Joined
Sep 19, 2018
Messages
60
Office Version
  1. 365
Platform
  1. Windows
Hi guys!
I have 30-minute interval data for the whole month of April that needs to be converted into 5-minute intervals. Eg. The value in B1 needs to be divided by 6 and put into column F1:F6, and the value in B2 needs to be divided by 6 and put in F7:F12. Is this able to be done in Excel? If so, how? Or is it something that could be done quicker in power query?

PQ april conversion to 5 min.xlsx
E
51/04/2023 0:20
Sheet1
Cell Formulas
RangeFormula
E5E5=E4+TIME(0,5,0)
 
As you can see, I have already converted the dates. What I needed help with was taking the 30 min value in B1, dividing it by 6 to give me a 5 min interval value to go in column F
I don't follow you... In column B I don't see any date or time information
 

Attachments

  • Screenshot 2024-01-23 124421.png
    Screenshot 2024-01-23 124421.png
    38.7 KB · Views: 9
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I didn't realise that the other place I posted the question was related to this forum.
It isn't related in any way. However some helpers may choose to frequent more than one forum. If you followed the link at the end of our rule as requested, you would understand the issue.
And just to emphasise, posting in two forums is not breaching any of our rules. It is just that we want to know about it if you do.
 
Upvote 0
I don't follow you... In column B I don't see any date or time information
7.81 (B1) is the value for the time period 1/4/2023 0:00 which is the first 30 min interval of the day. The site used 7.81 kWh of energy in that 30 min period. I need to convert all of the column B values into the value they would be if it was a 5 min interval. So 7.81 would need to be divided by 6 and that answer would need to populate F1:F6. Does that make sense?
 
Upvote 0
THIS MAKE SENSE!

So, try in F1 the following formula:
Excel Formula:
=IF(E21<(MAX(A:A)+TIME(0,30,0)),VLOOKUP(E1+0.0001,A:B,2)/6,NA())
Then copy down for as many dates you have in column F

Try...
 
Upvote 0
Solution
Are you looking for something like this?
If not please provide some small sample data and the expected results (entered manually) with XL2BB so that we can see exactly what you are trying to achieve.

MAP77.xlsm
ABCDEF
11/04/2023 0:007.811/04/2023 0:001.3017
21/04/2023 0:307.781/04/2023 0:051.3017
31/04/2023 1:008.251/04/2023 0:101.3017
41/04/2023 1:307.251/04/2023 0:151.3017
51/04/2023 2:007.271/04/2023 0:201.3017
61/04/2023 2:306.331/04/2023 0:251.3017
71/04/2023 3:007.381/04/2023 0:301.2967
81/04/2023 3:308.221/04/2023 0:351.2967
91/04/2023 4:007.751/04/2023 0:401.2967
101/04/2023 4:309.541/04/2023 0:451.2967
111/04/2023 5:007.991/04/2023 0:501.2967
121/04/2023 5:308.061/04/2023 0:551.2967
131/04/2023 6:007.971/04/2023 1:001.3750
141/04/2023 6:308.411/04/2023 1:051.3750
151/04/2023 7:009.021/04/2023 1:101.3750
161/04/2023 7:309.41/04/2023 1:151.3750
171/04/2023 8:008.981/04/2023 1:201.3750
181/04/2023 8:3012.21/04/2023 1:251.3750
191/04/2023 9:0012.771/04/2023 1:301.2083
201/04/2023 9:3012.651/04/2023 1:351.2083
211/04/2023 1:401.2083
221/04/2023 1:451.2083
231/04/2023 1:501.2083
241/04/2023 1:551.2083
251/04/2023 2:001.2117
261/04/2023 2:051.2117
271/04/2023 2:101.2117
281/04/2023 2:151.2117
291/04/2023 2:201.2117
301/04/2023 2:251.2117
311/04/2023 2:301.0550
321/04/2023 2:351.0550
331/04/2023 2:401.0550
341/04/2023 2:451.0550
351/04/2023 2:501.0550
361/04/2023 2:551.0550
371/04/2023 3:001.0550
381/04/2023 3:051.2300
391/04/2023 3:101.2300
401/04/2023 3:151.2300
411/04/2023 3:201.2300
421/04/2023 3:251.2300
431/04/2023 3:301.2300
441/04/2023 3:351.3700
451/04/2023 3:401.3700
461/04/2023 3:451.3700
471/04/2023 3:501.3700
481/04/2023 3:551.3700
491/04/2023 4:001.3700
501/04/2023 4:051.2917
511/04/2023 4:101.2917
521/04/2023 4:151.2917
531/04/2023 4:201.2917
541/04/2023 4:251.2917
551/04/2023 4:301.2917
561/04/2023 4:351.5900
571/04/2023 4:401.5900
581/04/2023 4:451.5900
591/04/2023 4:501.5900
601/04/2023 4:551.5900
611/04/2023 5:001.5900
621/04/2023 5:051.3317
631/04/2023 5:101.3317
641/04/2023 5:151.3317
651/04/2023 5:201.3317
661/04/2023 5:251.3317
671/04/2023 5:301.3317
681/04/2023 5:351.3433
691/04/2023 5:401.3433
701/04/2023 5:451.3433
711/04/2023 5:501.3433
721/04/2023 5:551.3433
731/04/2023 6:001.3433
741/04/2023 6:051.3283
751/04/2023 6:101.3283
761/04/2023 6:151.3283
771/04/2023 6:201.3283
781/04/2023 6:251.3283
791/04/2023 6:301.3283
801/04/2023 6:351.4017
811/04/2023 6:401.4017
821/04/2023 6:451.4017
831/04/2023 6:501.4017
841/04/2023 6:551.4017
851/04/2023 7:001.4017
861/04/2023 7:051.5033
871/04/2023 7:101.5033
881/04/2023 7:151.5033
891/04/2023 7:201.5033
901/04/2023 7:251.5033
911/04/2023 7:301.5033
921/04/2023 7:351.5667
931/04/2023 7:401.5667
941/04/2023 7:451.5667
951/04/2023 7:501.5667
961/04/2023 7:551.5667
971/04/2023 8:001.5667
981/04/2023 8:051.4967
991/04/2023 8:101.4967
1001/04/2023 8:151.4967
1011/04/2023 8:201.4967
1021/04/2023 8:251.4967
1031/04/2023 8:301.4967
1041/04/2023 8:352.0333
1051/04/2023 8:402.0333
1061/04/2023 8:452.0333
1071/04/2023 8:502.0333
1081/04/2023 8:552.0333
1091/04/2023 9:002.0333
1101/04/2023 9:052.1283
1111/04/2023 9:102.1283
1121/04/2023 9:152.1283
1131/04/2023 9:202.1283
1141/04/2023 9:252.1283
1151/04/2023 9:302.1283
1161/04/2023 9:352.1083
1171/04/2023 9:402.1083
1181/04/2023 9:452.1083
1191/04/2023 9:502.1083
1201/04/2023 9:552.1083
121
Sheet1
Cell Formulas
RangeFormula
E1:E120E1=SEQUENCE(ROWS(A1:A20)*6,,A1,TIME( ,5,0))
F1:F120F1=VLOOKUP(ROUND(FLOOR(E1#,1/24/2),15),A1:B20,2)/6
Dynamic array formulas.
 
Upvote 0
Are you looking for something like this?
If not please provide some small sample data and the expected results (entered manually) with XL2BB so that we can see exactly what you are trying to achieve.

MAP77.xlsm
ABCDEF
11/04/2023 0:007.811/04/2023 0:001.3017
21/04/2023 0:307.781/04/2023 0:051.3017
31/04/2023 1:008.251/04/2023 0:101.3017
41/04/2023 1:307.251/04/2023 0:151.3017
51/04/2023 2:007.271/04/2023 0:201.3017
61/04/2023 2:306.331/04/2023 0:251.3017
71/04/2023 3:007.381/04/2023 0:301.2967
81/04/2023 3:308.221/04/2023 0:351.2967
91/04/2023 4:007.751/04/2023 0:401.2967
101/04/2023 4:309.541/04/2023 0:451.2967
111/04/2023 5:007.991/04/2023 0:501.2967
121/04/2023 5:308.061/04/2023 0:551.2967
131/04/2023 6:007.971/04/2023 1:001.3750
141/04/2023 6:308.411/04/2023 1:051.3750
151/04/2023 7:009.021/04/2023 1:101.3750
161/04/2023 7:309.41/04/2023 1:151.3750
171/04/2023 8:008.981/04/2023 1:201.3750
181/04/2023 8:3012.21/04/2023 1:251.3750
191/04/2023 9:0012.771/04/2023 1:301.2083
201/04/2023 9:3012.651/04/2023 1:351.2083
211/04/2023 1:401.2083
221/04/2023 1:451.2083
231/04/2023 1:501.2083
241/04/2023 1:551.2083
251/04/2023 2:001.2117
261/04/2023 2:051.2117
271/04/2023 2:101.2117
281/04/2023 2:151.2117
291/04/2023 2:201.2117
301/04/2023 2:251.2117
311/04/2023 2:301.0550
321/04/2023 2:351.0550
331/04/2023 2:401.0550
341/04/2023 2:451.0550
351/04/2023 2:501.0550
361/04/2023 2:551.0550
371/04/2023 3:001.0550
381/04/2023 3:051.2300
391/04/2023 3:101.2300
401/04/2023 3:151.2300
411/04/2023 3:201.2300
421/04/2023 3:251.2300
431/04/2023 3:301.2300
441/04/2023 3:351.3700
451/04/2023 3:401.3700
461/04/2023 3:451.3700
471/04/2023 3:501.3700
481/04/2023 3:551.3700
491/04/2023 4:001.3700
501/04/2023 4:051.2917
511/04/2023 4:101.2917
521/04/2023 4:151.2917
531/04/2023 4:201.2917
541/04/2023 4:251.2917
551/04/2023 4:301.2917
561/04/2023 4:351.5900
571/04/2023 4:401.5900
581/04/2023 4:451.5900
591/04/2023 4:501.5900
601/04/2023 4:551.5900
611/04/2023 5:001.5900
621/04/2023 5:051.3317
631/04/2023 5:101.3317
641/04/2023 5:151.3317
651/04/2023 5:201.3317
661/04/2023 5:251.3317
671/04/2023 5:301.3317
681/04/2023 5:351.3433
691/04/2023 5:401.3433
701/04/2023 5:451.3433
711/04/2023 5:501.3433
721/04/2023 5:551.3433
731/04/2023 6:001.3433
741/04/2023 6:051.3283
751/04/2023 6:101.3283
761/04/2023 6:151.3283
771/04/2023 6:201.3283
781/04/2023 6:251.3283
791/04/2023 6:301.3283
801/04/2023 6:351.4017
811/04/2023 6:401.4017
821/04/2023 6:451.4017
831/04/2023 6:501.4017
841/04/2023 6:551.4017
851/04/2023 7:001.4017
861/04/2023 7:051.5033
871/04/2023 7:101.5033
881/04/2023 7:151.5033
891/04/2023 7:201.5033
901/04/2023 7:251.5033
911/04/2023 7:301.5033
921/04/2023 7:351.5667
931/04/2023 7:401.5667
941/04/2023 7:451.5667
951/04/2023 7:501.5667
961/04/2023 7:551.5667
971/04/2023 8:001.5667
981/04/2023 8:051.4967
991/04/2023 8:101.4967
1001/04/2023 8:151.4967
1011/04/2023 8:201.4967
1021/04/2023 8:251.4967
1031/04/2023 8:301.4967
1041/04/2023 8:352.0333
1051/04/2023 8:402.0333
1061/04/2023 8:452.0333
1071/04/2023 8:502.0333
1081/04/2023 8:552.0333
1091/04/2023 9:002.0333
1101/04/2023 9:052.1283
1111/04/2023 9:102.1283
1121/04/2023 9:152.1283
1131/04/2023 9:202.1283
1141/04/2023 9:252.1283
1151/04/2023 9:302.1283
1161/04/2023 9:352.1083
1171/04/2023 9:402.1083
1181/04/2023 9:452.1083
1191/04/2023 9:502.1083
1201/04/2023 9:552.1083
121
Sheet1
Cell Formulas
RangeFormula
E1:E120E1=SEQUENCE(ROWS(A1:A20)*6,,A1,TIME( ,5,0))
F1:F120F1=VLOOKUP(ROUND(FLOOR(E1#,1/24/2),15),A1:B20,2)/6
Dynamic array formulas.
Thanks @Peter_SSs. this worked also.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

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