Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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 1 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .