I’ve encountered a problem whilst trying to print dates using a for loop. The user inputs a start date and an end date, and the system prints dd/mm for each day between (and including) those dates into adjacent columns on a sheet.

The code works perfectly fine for dates of the same year. However when the dates span a year (e.g. 16/12/24 – 15/1/25) the for loop never ends.

I’ll put two examples in the comments with “//” between each example.

start_date = DateValue(InputBox("Please enter the starting date of the new pay cycle in d/m/yy format.", "Paycycle Start Date")) ' 16/7/24 // 16/12/24
end_date = DateValue(InputBox("Please enter the end date of the new pay cycle in d/m/yy format.", "Paycycle End Date")) ' 15/8/24 // 15/1/25

MsgBox DateDiff("d", start_date, end_date) ' 30 // 30 (as expected for both)

Set active_archive = ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Worksheets("Rota Builder"))

hc_date_headers = 2 ' counting variable to print dates in next column

For printing_dates = start_date To end_date
    printing_dates = Format(printing_dates, "dd/mm") 
    MsgBox printing_dates ' to prevent infinite loop.
    With active_archive.Cells(3, hc_date_headers)
        .Value = DateValue(printing_dates)
        .Font.Color = RGB(226, 239, 218)
    End With
    ' additional irrelevant code
    hc_date_headers = hc_date_headers + 1
Next ' stops once it reaches 15/8/24 // runs until I ctrl break.

I had a thought of using DateDiff to just set the for loop to be between two integers – and it does work from the little trial run I have done – but that would mean I’d have to reformat a painful amount of code, because I use hc_date_headers as a dynamic variable to create another table next to the one I create in this loop. So I thought I’d ask here to see if anybody knows what’s happening before I start reformatting.

Thanks! <3