Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?


share|improve this question
up vote 0 down vote accepted

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


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
share|improve this answer
Don't understand the downvote, this worked well, thanks! – JMK Jun 25 '12 at 14:04
@JMK: glad this worked for you! – jftuga Jun 25 '12 at 14:12

You need to use some simple functions.

  1. First, add the first two dates 01/06/2012, 15/06/2012
  2. On the third cell add this formula: =DATE(YEAR($A1),MONTH($A1) + 1, DAY($A1))
  3. Drag down the third cell.

That's about it.

share|improve this answer
+1 ...looks like JMK has Excel 2010 so EDATE is built-in and you could shorten that formula with =EDATE(A1,1) – barry houdini Jun 25 '12 at 20:59

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.