Convert 30 min interval data to 5 min intervals

[MAP77]

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]

The definition of the problem is weak, thus maybe this solution will not work for you, but try the following macro:

Sub Step5()
Dim I As Long, J As Long, FI As Long, FMins As Double
Dim Dest0 As Range
'
Set Dest0 = Range("F1")             '<<< Where the 5 minutes list will start from
FMins = TimeSerial(0, 5, 0)
On Error GoTo GErr
For I = 1 To Cells(Rows.Count, "B").End(xlUp).Row
    If IsDate(Cells(I, "B")) Then
        For J = 0 To 5
            FI = FI + 1
            Dest0.Cells(FI, 1).Value = Cells(I, "B").Value + J * FMins
        Next J
    Else
        Dest0.Cells(FI + 1, 1).Resize(6, 1).ClearContents
        FI = FI + 6
    End If
Next I
Beep
Exit Sub
'
GErr:
Debug.Print Now
Debug.Print "A:", I, J, FI
Debug.Print "B:", Cells(I, 2).Value, TypeName(Cells(I, 2).Value)
MsgBox ("Error processing line " & I & vbCrLf & "Process aborted")
End Sub

Copy the code into a "Standard Module" of your vba; the line marked <<< let you specify the starting address of the output list.
Then select the worksheet with your data and excecute Sub Step5
A "beep" will advice the macro has completed without error; or a msgbox will report on which line of column B the process has been halted

Try...

 

[MAP77]

The definition of the problem is weak, thus maybe this solution will not work for you, but try the following macro:

Sub Step5()
Dim I As Long, J As Long, FI As Long, FMins As Double
Dim Dest0 As Range
'
Set Dest0 = Range("F1")             '<<< Where the 5 minutes list will start from
FMins = TimeSerial(0, 5, 0)
On Error GoTo GErr
For I = 1 To Cells(Rows.Count, "B").End(xlUp).Row
    If IsDate(Cells(I, "B")) Then
        For J = 0 To 5
            FI = FI + 1
            Dest0.Cells(FI, 1).Value = Cells(I, "B").Value + J * FMins
        Next J
    Else
        Dest0.Cells(FI + 1, 1).Resize(6, 1).ClearContents
        FI = FI + 6
    End If
Next I
Beep
Exit Sub
'
GErr:
Debug.Print Now
Debug.Print "A:", I, J, FI
Debug.Print "B:", Cells(I, 2).Value, TypeName(Cells(I, 2).Value)
MsgBox ("Error processing line " & I & vbCrLf & "Process aborted")
End Sub

Copy the code into a "Standard Module" of your vba; the line marked <<< let you specify the starting address of the output list.
Then select the worksheet with your data and excecute Sub Step5
A "beep" will advice the macro has completed without error; or a msgbox will report on which line of column B the process has been halted

Try...

Hi Anthony,
Thanks for this. I ran the macro, and it beeped, but nothing happened. I changed the Range from ("F1") to ("F1:F8640") but everything else was the same. Sorry, I'm not very familiar with macros but no error came up.

 

[Anthony47]

Can you show the content of say B1:B5, using XL2BB?
Can you tell me the name of the vba module where the code has been inserted (look into the head of the vba window)
Also, if you format B1 as "Number with two decimals", how the cell is displayed?
If you wish to create the list starting from F1 then leave Set Dest0 = Range("F1")

 

[awoohaw]

Do you need a vba solution?

 

[MAP77]

Sorry, I only just realised I didn't copy the file properly.

PQ april conversion to 5 min.xlsm
	A	B	C	D	E
1	1/04/2023 0:00	7.81			1/04/2023 0:00
2	1/04/2023 0:30	7.78			1/04/2023 0:05
3	1/04/2023 1:00	8.25			1/04/2023 0:10
4	1/04/2023 1:30	7.25			1/04/2023 0:15
5	1/04/2023 2:00	7.27			1/04/2023 0:20
6	1/04/2023 2:30	6.33			1/04/2023 0:25
7	1/04/2023 3:00	7.38			1/04/2023 0:30
8	1/04/2023 3:30	8.22			1/04/2023 0:35
9	1/04/2023 4:00	7.75			1/04/2023 0:40
10	1/04/2023 4:30	9.54			1/04/2023 0:45
11	1/04/2023 5:00	7.99			1/04/2023 0:50
12	1/04/2023 5:30	8.06			1/04/2023 0:55
13	1/04/2023 6:00	7.97			1/04/2023 1:00
14	1/04/2023 6:30	8.41			1/04/2023 1:05
15	1/04/2023 7:00	9.02			1/04/2023 1:10
16	1/04/2023 7:30	9.4			1/04/2023 1:15
17	1/04/2023 8:00	8.98			1/04/2023 1:20
18	1/04/2023 8:30	12.2			1/04/2023 1:25
19	1/04/2023 9:00	12.77			1/04/2023 1:30
20	1/04/2023 9:30	12.65			1/04/2023 1:35
Sheet1

Cell Formulas
Range	Formula
E2:E20	E2	=E1+TIME(0,5,0)

 

[Peter_SSs]

Reminder:
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel Forum
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Anthony47]

In your first message you wrote that the dates to convert are in column B, but from your minisheet they seem to be in column A.
In this case you need to adapt the macro for column A:

Sub Step5()
Dim I As Long, J As Long, FI As Long, FMins As Double
Dim Dest0 As Range
'
Set Dest0 = Range("F1")             '<<< Where the 5 minutes list will start from
FMins = TimeSerial(0, 5, 0)
On Error GoTo GErr
For I = 1 To Cells(Rows.Count, "A").End(xlUp).Row
'    Debug.Print I, TypeName(Cells(I, "A").Value), Cells(I, "A").Value
    If IsDate(Cells(I, "B")) Then
        For J = 0 To 5
            FI = FI + 1
            Dest0.Cells(FI, 1).Value = Cells(I, "A").Value + J * FMins
        Next J
    Else
        Dest0.Cells(FI + 1, 1).Resize(6, 1).ClearContents
        FI = FI + 6
    End If
Next I
Exit Sub
'
GErr:
Debug.Print Now
Debug.Print "A:", I, J, FI
Debug.Print "B:", Cells(I, 1).Value, TypeName(Cells(I, 1).Value)
MsgBox ("Error processing line " & I & vbCrLf & "Process aborted")
End Sub

 

[MAP77]

Sorry for the confusion, I didn't realise you couldn't see the file. 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.

 

[MAP77]

Reminder:
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel Forum
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

I didn't realise that the other place I posted the question was related to this forum. I posted it in the other area because that's where I did my Excel course and Mynda had helped me with a similar problem.