0

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

2 Answers 2

2

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

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.

1
  • 1
    +1 ...looks like JMK has Excel 2010 so EDATE is built-in and you could shorten that formula with =EDATE(A1,1) Jun 25, 2012 at 20:59

You must log in to answer this question.

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