Split time range in 1 hour intervals-CodePudding

want to split a time range into 1 hour intervals

enter image description here

split the given time range into 1 hour intervals from cell A2 and A3, the time range will be changed a serval time and on a (Macro) click it should split the given time range into 1 hour intervals.

CodePudding user response：

Create an Hourly Sequence

Sub CreateHourlySequence()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim dt1: dt1 = ws.Range("A2").Value
    Dim dt2: dt2 = ws.Range("A3").Value
    
    Dim dfCell As Range: Set dfCell = ws.Range("C2")
    dfCell.Resize(ws.Rows.Count - dfCell.Row   1).ClearContents

    Select Case False
        Case IsDate(dt1), IsDate(dt2): Exit Sub
    End Select
    
    Dim dtDiff As Long: dtDiff = DateDiff("h", dt1, dt2)
    
    Dim dtStart As Date, dStep As Long
    
    Select Case dtDiff
        Case Is > 0: dtStart = dt1: dStep = 1
        Case Is < 0: dtStart = dt2: dStep = -1
    End Select
    
    Dim rCount As Long: rCount = Abs(dtDiff)   1
    Dim Data() As Date: ReDim Data(1 To rCount, 1 To 1)
    
    Dim d As Long, r As Long
    
    If dStep = 0 Then
        Data(1, 1) = dtStart
    Else
        For d = 0 To dtDiff Step dStep
            r = r   1
            Data(r, 1) = DateAdd("h", d, dtStart)
        Next d
    End If
    
    dfCell.Resize(rCount).Value = Data
    
End Sub

CodePudding user response：

If you are ok with a non-VBA solution, then you have some options.

Option 1: SEQUENCE

For the Excel version listed

Then, in C2, you could have :

=SEQUENCE((A3-A2)/VALUE("01:00:00") 1,1,A2,VALUE("01:00:00"))

Option 2: Dynamic Array Formula

You have an Excel version listed here, you can use a dynamic array formula .

Example: Making the same assumptions as option 1, for where the data is, you could use a formula like this one:

=(ROW(INDIRECT("1:"&(A3-A2)/VALUE("01:00:00") 1))-1)*VALUE("01:00:00") A2

Option 3: Old array formula

Same idea as option 3 but using the old array formula explained here.

CodePudding user response：

A Simple Solution given your example (to clear cells it is your job :-) It would be better to write to an array but as example it should be ok.

Option Explicit
    
    Sub TimeToHour()
    Dim startTime As Double, endTime As Double, i As Double, z As Double
    startTime = Range("a2")
    endTime = Range("a3")
    Columns(3).NumberFormat = Range("a2").NumberFormat ' Column C
    z = 2
    For i = startTime To endTime Step 1 / 24
    Cells(z, 3) = i ' write to column c starting in row 2
    z = z   1
    Next
    End Sub