Populate a column with dates starting from 1 jan to 31 dec given the year

Question

I want to write a code in excel-vba which populates the dates for a particular year in a column on the sheet.
The code should also take into account leap years. Say, I have entered the year in Cell B1 (as 2020), so Column A should be populated with 366 date entries(taking into account the leap year).

Excel has that out of the box (fill/series), but if you want to reprogram it, feel free. But - what is your question? — Aganju
Basically excel don't take into account the leap year but with the help of VBA, I think that can be done. I if fill 366 rows with excel then the last row will be "#Value" or something like it for 3 years out of 4 years — Karan

Gary's Student Gary's Student · Accepted Answer · 2018-09-07T11:55:33

You can loop on a Date variable:

Sub dater()
    Dim yr As Long, d As Date, i As Long
    yr = Range("B1").Value
    For d = DateSerial(yr, 1, 1) To DateSerial(yr, 12, 31)
        i = i + 1
        Cells(i, 1) = d
    Next d
End Sub

This will automatically handle leap-years and populate either 366 or 365 cells as appropriate.

(format column A to suit your needs)

Populate a column with dates starting from 1 jan to 31 dec given the year

1 Answers