autofill dates in sequence in excel

By lharwell ·
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:


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?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Well I have a Gomer method

by LocoLobo In reply to autofill dates in sequenc ...

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.

Collapse -

You're only as good as the tools available to you!

by Absolutely In reply to Well I have a Gomer metho ...

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.

Collapse -

Quick way

by Bizzo In reply to autofill dates in sequenc ...

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.

Related Discussions

Related Forums