How to repeat date in column then increase

[silkyboots32]

HI there!

I hope someone can shed light on this what I thought would be simple task.

I have some data entry to do and I have 9 entries with the same date then go on to the next day. I am working with excel 2007 and 2010, so I would like it to be compatible with both.

Column 2 is repeating with 9 different criteria, if that helps

So I would like the end result something like this:

[TABLE="width: 60"]
<TBODY>[TR]
[TD="width: 80, bgcolor: transparent"]Date
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="1" Year="2012">12/1/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="1" Year="2012">12/1/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="1" Year="2012">12/1/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="1" Year="2012">12/1/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="1" Year="2012">12/1/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="1" Year="2012">12/1/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="1" Year="2012">12/1/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="1" Year="2012">12/1/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="1" Year="2012">12/1/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="2" Year="2012">12/2/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="2" Year="2012">12/2/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="2" Year="2012">12/2/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="2" Year="2012">12/2/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="2" Year="2012">12/2/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="2" Year="2012">12/2/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="2" Year="2012">12/2/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]<st1:date ls="trans" Month="12" Day="2" Year="2012">12/2/2012</st1:date>
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]
<st1:date ls="trans" Month="12" Day="2" Year="2012">12/2/2012</st1:date>

and so on

​

Thank you in Advance[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Scott Huish]

Put your first date in A1.

Then in A2:
=$A$1+INT(ROWS($A$1:A1)/9)

Format as date. Copy down.

 

[rallcorn]

Welcome to the board!
With 12/1/12 in A1, this formula in A2:
=IF(COUNTIF($A$1:A1,A1)<9,A1,A1+1) & copy down in Col A
(Note placement of $ signs!)

 

[silkyboots32]

Works great! How do I change it to constant, so its not a formula anymore, so my data will not accidently change if insert a row accidently?

 

[rallcorn]

Select cells, Ctrl+C, then hold down Alt and press (in succession) E, S & V
(This will paste special, VALUES)