VBA Looping through dates and times?

hearsay9999

New Member
Joined
Jun 24, 2021
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Good afternoon, I'm relatively new to VBA and brand new to this forum, (this is my first post). I have been struggling to find a way to line up a dynamic range with a static range. I have looked around on this and other forums for code that might help me with what I'm trying to achieve but so for no luck. Can anyone help?

  • Column A - Dynamic date range, the number of dates listed will change.
  • Column B - Static time range, this will not change and will begin with 00:00 and end with 23:30 going up by increments of 30mins
  • Column D - This needs to be each date in column A replicated 48 times (for each 30 min interval in the day)
  • Column E - This needs to be each time interval from 00:00 - 23:30 repeated for as many dates showing in column D.
sample screenshot: On the 49th row, the next day will need to start in column D and in column E, the time will need to begin again at 00:00

1624564208883.png
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,002
Here's one way.
VBA Code:
Public Sub Generate_Dates_Times()

    Dim dates As Variant, times As Variant
    Dim d As Long, t As Long, r As Long
   
    With ActiveSheet
        dates = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Value
        times = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp)).Value
        r = 1
        For d = 1 To UBound(dates)
            For t = 1 To UBound(times)
                r = r + 1
                .Cells(r, "D").Value = dates(d, 1)
                .Cells(r, "E").Value = times(t, 1)
            Next
        Next
        .Range("D2:D" & r).NumberFormat = .Range("A2").NumberFormat
        .Range("E2:E" & r).NumberFormat = .Range("B2").NumberFormat

    End With

End Sub
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
865
Office Version
  1. 365
Platform
  1. Windows
This could be done without VBA if you know the maximum number of dates that could be put in A.

D2:
Excel Formula:
=IF(ROW()-1>COUNT(A:A)*48,"",INDEX(A:A,1+INT((ROW()-2)/48)+1,1))

E2:
Excel Formula:
=IF(ROW()-1>COUNT(A:A)*48,"",MOD(ROW()-2,48)*TIME(0,30,0))

Copy formulas down to row (max dates) x 48
 

hearsay9999

New Member
Joined
Jun 24, 2021
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Public Sub Generate_Dates_Times() Dim dates As Variant, times As Variant Dim d As Long, t As Long, r As Long With ActiveSheet dates = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Value times = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp)).Value r = 1 For d = 1 To UBound(dates) For t = 1 To UBound(times) r = r + 1 .Cells(r, "D").Value = dates(d, 1) .Cells(r, "E").Value = times(t, 1) Next Next .Range("D2:D" & r).NumberFormat = .Range("A2").NumberFormat .Range("E2:E" & r).NumberFormat = .Range("B2").NumberFormat End With End Sub
Fantastic!!! Thank you VERY much John! This does exactly what I was hoping for!!
 

hearsay9999

New Member
Joined
Jun 24, 2021
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This could be done without VBA if you know the maximum number of dates that could be put in A.

D2:
Excel Formula:
=IF(ROW()-1>COUNT(A:A)*48,"",INDEX(A:A,1+INT((ROW()-2)/48)+1,1))

E2:
Excel Formula:
=IF(ROW()-1>COUNT(A:A)*48,"",MOD(ROW()-2,48)*TIME(0,30,0))

Copy formulas down to row (max dates) x 48
Thank you VERY much!! Unfortunately I'm not certain how many dates will be entered. I really appreciate the reply!
 

Forum statistics

Threads
1,141,480
Messages
5,706,638
Members
421,460
Latest member
Taamrak

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
Top