Convert 30 min interval data to 5 min intervals

MAP77 · Jan 21, 2024

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

5

1/04/2023 0:20

Sheet1

Cell Formulas
Range	Formula
E5	E5	=E4+TIME(0,5,0)

Anthony47 · Jan 23, 2024

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

Peter_SSs · Jan 23, 2024

MAP77 said:
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.

MAP77 · Jan 24, 2024

Anthony47 said:
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?

Anthony47 · Jan 24, 2024

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

Peter_SSs · Jan 24, 2024

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

A

B

C

D

E

F

1

1/04/2023 0:00

7.81

1/04/2023 0:00

1.3017

2

1/04/2023 0:30

7.78

1/04/2023 0:05

1.3017

3

1/04/2023 1:00

8.25

1/04/2023 0:10

1.3017

4

1/04/2023 1:30

7.25

1/04/2023 0:15

1.3017

5

1/04/2023 2:00

7.27

1/04/2023 0:20

1.3017

6

1/04/2023 2:30

6.33

1/04/2023 0:25

1.3017

7

1/04/2023 3:00

7.38

1/04/2023 0:30

1.2967

8

1/04/2023 3:30

8.22

1/04/2023 0:35

1.2967

9

1/04/2023 4:00

7.75

1/04/2023 0:40

1.2967

10

1/04/2023 4:30

9.54

1/04/2023 0:45

1.2967

11

1/04/2023 5:00

7.99

1/04/2023 0:50

1.2967

12

1/04/2023 5:30

8.06

1/04/2023 0:55

1.2967

13

1/04/2023 6:00

7.97

1/04/2023 1:00

1.3750

14

1/04/2023 6:30

8.41

1/04/2023 1:05

1.3750

15

1/04/2023 7:00

9.02

1/04/2023 1:10

1.3750

16

1/04/2023 7:30

9.4

1/04/2023 1:15

1.3750

17

1/04/2023 8:00

8.98

1/04/2023 1:20

1.3750

18

1/04/2023 8:30

12.2

1/04/2023 1:25

1.3750

19

1/04/2023 9:00

12.77

1/04/2023 1:30

1.2083

20

1/04/2023 9:30

12.65

1/04/2023 1:35

1.2083

21

1/04/2023 1:40

1.2083

22

1/04/2023 1:45

1.2083

23

1/04/2023 1:50

1.2083

24

1/04/2023 1:55

1.2083

25

1/04/2023 2:00

1.2117

26

1/04/2023 2:05

1.2117

27

1/04/2023 2:10

1.2117

28

1/04/2023 2:15

1.2117

29

1/04/2023 2:20

1.2117

30

1/04/2023 2:25

1.2117

31

1/04/2023 2:30

1.0550

32

1/04/2023 2:35

1.0550

33

1/04/2023 2:40

1.0550

34

1/04/2023 2:45

1.0550

35

1/04/2023 2:50

1.0550

36

1/04/2023 2:55

1.0550

37

1/04/2023 3:00

1.0550

38

1/04/2023 3:05

1.2300

39

1/04/2023 3:10

1.2300

40

1/04/2023 3:15

1.2300

41

1/04/2023 3:20

1.2300

42

1/04/2023 3:25

1.2300

43

1/04/2023 3:30

1.2300

44

1/04/2023 3:35

1.3700

45

1/04/2023 3:40

1.3700

46

1/04/2023 3:45

1.3700

47

1/04/2023 3:50

1.3700

48

1/04/2023 3:55

1.3700

49

1/04/2023 4:00

1.3700

50

1/04/2023 4:05

1.2917

51

1/04/2023 4:10

1.2917

52

1/04/2023 4:15

1.2917

53

1/04/2023 4:20

1.2917

54

1/04/2023 4:25

1.2917

55

1/04/2023 4:30

1.2917

56

1/04/2023 4:35

1.5900

57

1/04/2023 4:40

1.5900

58

1/04/2023 4:45

1.5900

59

1/04/2023 4:50

1.5900

60

1/04/2023 4:55

1.5900

61

1/04/2023 5:00

1.5900

62

1/04/2023 5:05

1.3317

63

1/04/2023 5:10

1.3317

64

1/04/2023 5:15

1.3317

65

1/04/2023 5:20

1.3317

66

1/04/2023 5:25

1.3317

67

1/04/2023 5:30

1.3317

68

1/04/2023 5:35

1.3433

69

1/04/2023 5:40

1.3433

70

1/04/2023 5:45

1.3433

71

1/04/2023 5:50

1.3433

72

1/04/2023 5:55

1.3433

73

1/04/2023 6:00

1.3433

74

1/04/2023 6:05

1.3283

75

1/04/2023 6:10

1.3283

76

1/04/2023 6:15

1.3283

77

1/04/2023 6:20

1.3283

78

1/04/2023 6:25

1.3283

79

1/04/2023 6:30

1.3283

80

1/04/2023 6:35

1.4017

81

1/04/2023 6:40

1.4017

82

1/04/2023 6:45

1.4017

83

1/04/2023 6:50

1.4017

84

1/04/2023 6:55

1.4017

85

1/04/2023 7:00

1.4017

86

1/04/2023 7:05

1.5033

87

1/04/2023 7:10

1.5033

88

1/04/2023 7:15

1.5033

89

1/04/2023 7:20

1.5033

90

1/04/2023 7:25

1.5033

91

1/04/2023 7:30

1.5033

92

1/04/2023 7:35

1.5667

93

1/04/2023 7:40

1.5667

94

1/04/2023 7:45

1.5667

95

1/04/2023 7:50

1.5667

96

1/04/2023 7:55

1.5667

97

1/04/2023 8:00

1.5667

98

1/04/2023 8:05

1.4967

99

1/04/2023 8:10

1.4967

100

1/04/2023 8:15

1.4967

101

1/04/2023 8:20

1.4967

102

1/04/2023 8:25

1.4967

103

1/04/2023 8:30

1.4967

104

1/04/2023 8:35

2.0333

105

1/04/2023 8:40

2.0333

106

1/04/2023 8:45

2.0333

107

1/04/2023 8:50

2.0333

108

1/04/2023 8:55

2.0333

109

1/04/2023 9:00

2.0333

110

1/04/2023 9:05

2.1283

111

1/04/2023 9:10

2.1283

112

1/04/2023 9:15

2.1283

113

1/04/2023 9:20

2.1283

114

1/04/2023 9:25

2.1283

115

1/04/2023 9:30

2.1283

116

1/04/2023 9:35

2.1083

117

1/04/2023 9:40

2.1083

118

1/04/2023 9:45

2.1083

119

1/04/2023 9:50

2.1083

120

1/04/2023 9:55

2.1083

121

Sheet1

Cell Formulas
Range	Formula
E1:E120	E1	=SEQUENCE(ROWS(A1:A20)*6,,A1,TIME( ,5,0))
F1:F120	F1	=VLOOKUP(ROUND(FLOOR(E1#,1/24/2),15),A1:B20,2)/6
Dynamic array formulas.

MAP77 · Jan 24, 2024

Anthony47 said:
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...

This worked. Thanks @Anthony47

MAP77 · Jan 24, 2024

Peter_SSs said:
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
A B C D E F
1 1/04/2023 0:00 7.81 1/04/2023 0:00 1.3017
2 1/04/2023 0:30 7.78 1/04/2023 0:05 1.3017
3 1/04/2023 1:00 8.25 1/04/2023 0:10 1.3017
4 1/04/2023 1:30 7.25 1/04/2023 0:15 1.3017
5 1/04/2023 2:00 7.27 1/04/2023 0:20 1.3017
6 1/04/2023 2:30 6.33 1/04/2023 0:25 1.3017
7 1/04/2023 3:00 7.38 1/04/2023 0:30 1.2967
8 1/04/2023 3:30 8.22 1/04/2023 0:35 1.2967
9 1/04/2023 4:00 7.75 1/04/2023 0:40 1.2967
10 1/04/2023 4:30 9.54 1/04/2023 0:45 1.2967
11 1/04/2023 5:00 7.99 1/04/2023 0:50 1.2967
12 1/04/2023 5:30 8.06 1/04/2023 0:55 1.2967
13 1/04/2023 6:00 7.97 1/04/2023 1:00 1.3750
14 1/04/2023 6:30 8.41 1/04/2023 1:05 1.3750
15 1/04/2023 7:00 9.02 1/04/2023 1:10 1.3750
16 1/04/2023 7:30 9.4 1/04/2023 1:15 1.3750
17 1/04/2023 8:00 8.98 1/04/2023 1:20 1.3750
18 1/04/2023 8:30 12.2 1/04/2023 1:25 1.3750
19 1/04/2023 9:00 12.77 1/04/2023 1:30 1.2083
20 1/04/2023 9:30 12.65 1/04/2023 1:35 1.2083
21 1/04/2023 1:40 1.2083
22 1/04/2023 1:45 1.2083
23 1/04/2023 1:50 1.2083
24 1/04/2023 1:55 1.2083
25 1/04/2023 2:00 1.2117
26 1/04/2023 2:05 1.2117
27 1/04/2023 2:10 1.2117
28 1/04/2023 2:15 1.2117
29 1/04/2023 2:20 1.2117
30 1/04/2023 2:25 1.2117
31 1/04/2023 2:30 1.0550
32 1/04/2023 2:35 1.0550
33 1/04/2023 2:40 1.0550
34 1/04/2023 2:45 1.0550
35 1/04/2023 2:50 1.0550
36 1/04/2023 2:55 1.0550
37 1/04/2023 3:00 1.0550
38 1/04/2023 3:05 1.2300
39 1/04/2023 3:10 1.2300
40 1/04/2023 3:15 1.2300
41 1/04/2023 3:20 1.2300
42 1/04/2023 3:25 1.2300
43 1/04/2023 3:30 1.2300
44 1/04/2023 3:35 1.3700
45 1/04/2023 3:40 1.3700
46 1/04/2023 3:45 1.3700
47 1/04/2023 3:50 1.3700
48 1/04/2023 3:55 1.3700
49 1/04/2023 4:00 1.3700
50 1/04/2023 4:05 1.2917
51 1/04/2023 4:10 1.2917
52 1/04/2023 4:15 1.2917
53 1/04/2023 4:20 1.2917
54 1/04/2023 4:25 1.2917
55 1/04/2023 4:30 1.2917
56 1/04/2023 4:35 1.5900
57 1/04/2023 4:40 1.5900
58 1/04/2023 4:45 1.5900
59 1/04/2023 4:50 1.5900
60 1/04/2023 4:55 1.5900
61 1/04/2023 5:00 1.5900
62 1/04/2023 5:05 1.3317
63 1/04/2023 5:10 1.3317
64 1/04/2023 5:15 1.3317
65 1/04/2023 5:20 1.3317
66 1/04/2023 5:25 1.3317
67 1/04/2023 5:30 1.3317
68 1/04/2023 5:35 1.3433
69 1/04/2023 5:40 1.3433
70 1/04/2023 5:45 1.3433
71 1/04/2023 5:50 1.3433
72 1/04/2023 5:55 1.3433
73 1/04/2023 6:00 1.3433
74 1/04/2023 6:05 1.3283
75 1/04/2023 6:10 1.3283
76 1/04/2023 6:15 1.3283
77 1/04/2023 6:20 1.3283
78 1/04/2023 6:25 1.3283
79 1/04/2023 6:30 1.3283
80 1/04/2023 6:35 1.4017
81 1/04/2023 6:40 1.4017
82 1/04/2023 6:45 1.4017
83 1/04/2023 6:50 1.4017
84 1/04/2023 6:55 1.4017
85 1/04/2023 7:00 1.4017
86 1/04/2023 7:05 1.5033
87 1/04/2023 7:10 1.5033
88 1/04/2023 7:15 1.5033
89 1/04/2023 7:20 1.5033
90 1/04/2023 7:25 1.5033
91 1/04/2023 7:30 1.5033
92 1/04/2023 7:35 1.5667
93 1/04/2023 7:40 1.5667
94 1/04/2023 7:45 1.5667
95 1/04/2023 7:50 1.5667
96 1/04/2023 7:55 1.5667
97 1/04/2023 8:00 1.5667
98 1/04/2023 8:05 1.4967
99 1/04/2023 8:10 1.4967
100 1/04/2023 8:15 1.4967
101 1/04/2023 8:20 1.4967
102 1/04/2023 8:25 1.4967
103 1/04/2023 8:30 1.4967
104 1/04/2023 8:35 2.0333
105 1/04/2023 8:40 2.0333
106 1/04/2023 8:45 2.0333
107 1/04/2023 8:50 2.0333
108 1/04/2023 8:55 2.0333
109 1/04/2023 9:00 2.0333
110 1/04/2023 9:05 2.1283
111 1/04/2023 9:10 2.1283
112 1/04/2023 9:15 2.1283
113 1/04/2023 9:20 2.1283
114 1/04/2023 9:25 2.1283
115 1/04/2023 9:30 2.1283
116 1/04/2023 9:35 2.1083
117 1/04/2023 9:40 2.1083
118 1/04/2023 9:45 2.1083
119 1/04/2023 9:50 2.1083
120 1/04/2023 9:55 2.1083
121
Sheet1
Cell Formulas
Range Formula
E1:E120 E1 =SEQUENCE(ROWS(A1:A20)*6,,A1,TIME( ,5,0))
F1:F120 F1 =VLOOKUP(ROUND(FLOOR(E1#,1/24/2),15),A1:B20,2)/6
Dynamic array formulas.

Thanks @Peter_SSs. this worked also.

Peter_SSs · Jan 25, 2024

You're welcome. Glad to contribute.

Anthony47 · Jan 25, 2024

Thank you for the feedback

Convert 30 min interval data to 5 min intervals

MAP77

Board Regular

Anthony47

Well-known Member

Attachments

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

MAP77

Board Regular

Anthony47

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

MAP77

Board Regular

MAP77

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Anthony47

Well-known Member

Similar threads

Forum statistics

Share this page

Convert 30 min interval data to 5 min intervals

Board Regular

Well-known Member

Attachments

Excel Facts

MrExcel MVP, Moderator

Board Regular

Well-known Member

MrExcel MVP, Moderator

Board Regular

Board Regular

MrExcel MVP, Moderator

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock