Excel – Autofill date pattern with Excel

microsoft excelworksheet-function

I want to use Excel to fill a range of dates, the first and 15th of each month to be precise.

My spreadsheet currently looks like so:

Current Spreadsheet

When I drag down, it looks like so:

New Spreadsheet

Instead of continuing the pattern, it just repeats what's there. I have tried each of the following AutoFill options to no avail:

  • Copy Cells
  • Fill Series
  • Fill Formatting Only
  • Fill Without Formatting
  • Fill Days
  • Fill Weekdays
  • Fill Months
  • Fill Years

What am I doing wrong?

Thanks

Best Answer

I was able to write a Excel Macro to accomplish this:

dates

Sub AddEntry()
    yr = "2012"
    x = ActiveCell.Row
    y = ActiveCell.Column
    For mon = 6 To 12
            Cells(x, y) = "01/" + CStr(mon) + "/" + yr
            Cells(x + 1, y) = "15/" + CStr(mon) + "/" + yr
            x = x + 2
            Cells(x, y).Select
    Next mon
End Sub

I actually used this to see to correct formatting, note the single quote. But this changes the entry from a date to a string.

Cells(x, y) = "'01/" + CStr(mon) + "/" + yr
Cells(x + 1, y) = "'15/" + CStr(mon) + "/" + yr
Related Question