excelvbadatetimenested-loops

VBA code to Fill excel rows in column with dates and time, using only weeksdays and per day displaying 13 hours incremented by 15 minutes

I'm trying to fill rows in several columns with dates and time from given start date to given end date to create a schedule. In the first column I want the date displayed as "year", 2nd column as "month", 3rd column as "dddd d MMMM yyyy" and 5th column as "hh:mm".

Per day, I want to have values from 8:00 until 20:45. And I only want to see weekdays (Monday to Friday)

I managed to find code for filling the time part where it will fill all the times from 8:00 to 20:45 within the start date. But I'm struggling to get the next day after the last pasted value from the time part.

Then the next challenge is to only get weekdays.

My code looks like this:

Sub insert_new_dates()




dtIncrT = 15                                    'minutes to add each cell
dtIncrD = 1                                     'add day
intCellCnt = 780 / dtIncrT                     '13h * 60m = 780 minutes per day
dtDate = CDate("01/07/2025 08:00:00")          'start date+time for first cell
dtDay = dtDate + dtIncrD
EndDate = CDate("05/07/2025 08:00:00")

For a = dtDate To EndDate

For i = 1 To intCellCnt                         'loop through n cells
    Cells(i, 1) = Format(datDate, "yyyy")      'write and format result
    Cells(i, 2) = Format(datDate, "MMMM")
    Cells(i, 3) = Format(datDate, "dddd d MMMM yyyy")
    Cells(i, 5) = Format(datDate, "hh:mm")
    datDate = DateAdd("n", dtIncrT, dtDate)    'add increment value

Next i
i = i + 1
Next a


End Sub

I want it to look like this:

[Example]

But my code just overwrites all values with the next day instead of pasting it to the row after the last time was pasted. So basically, it fills the same cells as the initial loop

Solution

  • If you have 365, this can be done with a worksheet formula in a single cell. The results will spill into the adjacent rows and columns. No VBA required.

    Note that the first four rows, although hard-coded, could easily refer to worksheet cells where you enter the required parameters.

    =LET(
    dtStart, DATE(2025, 7, 1),
    dtEnd, DATE(2025, 7, 5),
    Tincr, 15,
    hrs_day, 13,
    Times, TIME(8, SEQUENCE(hrs_day * 60 / Tincr, , 0, Tincr), 0),
    wkDys, NETWORKDAYS(dtStart, dtEnd),
    dys, WORKDAY(dtStart, SEQUENCE(wkDys, , 0)),
    dt_rws, INT(SEQUENCE(COUNT(Times) * COUNT(dys), , 0) / COUNT(Times)) + 1,
    allDts, CHOOSEROWS(dys, dt_rws),
    tm_rws, MOD(SEQUENCE(COUNT(Times) * COUNT(dys), , 0), COUNT(Times)) + 1,
    allTms, CHOOSEROWS(Times, tm_rws),
    dt_tms, allDts + allTms,
    res, HSTACK(
        YEAR(dt_tms),
        TEXT(dt_tms, "mmmm"),
        TEXT(dt_tms, "ddddd d mmmm yyyy"),
        TEXT(dt_tms, "hh:mm")
    ),
    res
)

    Partial results
    enter image description here