Question Locked

autofill dates in sequence in excel

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am creating a spreadsheet that will need to have dates autofilled. I need each day of the month in sequence with each day entered 3 times to signify 3 shifts. For example:

06/01/07
06/01/07
06/01/07
06/02/07
06/02/07
06/02/07

to the end of each month. There will be a new workbook for each month & there will be several worksheets (tabs) in each monthly workbook. Is it possible for excel to autofill this way?

4 replies

by locolobo

Hopefully someone else has an elegant way to do it. If someone does I would like to see it also.

Let's say your dates are going to be in cells A1:A93. In cells Z2:Z93 build an ordinary sequence of numbers from 1 to 92. In cell Y1 type "=TRUNC(Z1/3,0)". Copy the formula down and you should see the numbers 1 through 30 repeated 3 times each.

You probably already see where this is going but, in cell A1 enter your date (6/1/2007). In cell A2 enter "=$A$1+Y1". Copy that formula down. Klugy isn't it, but it worked. It's up to you but I would copy and paste special the dates where you want them and delete the evidence of my kluginess.

by absolutely

The Gomer method I'd use is to use the typical method to create 1 copy of each of the 30 dates, copy the entire selection then paste it to the bottom of the same column, twice. Then, select the entire column, copy & paste special/values (right-click & format the column's numbers as text also, if necessary).

Data menu/sort.

When I had to use Excel, I'd call my Gomer methods "good" if they could be recorded as keyboard macros.

by bizzo

In cells A1, A2, A3, put your first date.
In cell A4 put =A1+1
Highlight cell A4, and drag the bottom right corner down to autofill for the rest of the month.

This discussion is closed to new replies.