Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Autofill every Thursday using Excel- how do you do it?

Sep 3, 2005 6:31AM PDT

I want to create a list of dates for each Thursday of our school year so I can plan assemblies. The first one starts on 08/09/05. I've entered this start date - how do I autofill with the next Thursday (15/09/05) and so on until September 2006?

Hope someone can help.

Discussion is locked

- Collapse -
Re: autofill each Thursday
Sep 3, 2005 6:37AM PDT
- Collapse -
Auto sequences
Sep 5, 2005 12:30PM PDT

Excel has a very interesting ability to autofill additional cells once you highlight two adjacent cells and drag the bottom-right handle (shows as a +).

What Excel does is look at the difference between the two values and automatically increments to fill all the additional cells you drag that handle over.

This works for numbers or dates.

That means if you put 1 then 2 into two adjacent cells, highlight them, then drag the lower right +.
As you drag you will see the cursor field indicate the values that will be entered if you let the left-mouse button go. Such as 3,4,5,6,7,8,9, etc

If you highlight two cells with two numbers like 1,3 you will seethe cursor go out to 5,7,9,11, etc.

Or with 5 and 10, you get 15,20,25,30,35,40, etc. (a gap of 5)

Or with 1 and 10, you get 19,28,37,46,etc. (a gap of 9)

This also works with dates.
If you enter in one Thursday (in your example you used 08/09/05 you must be in Europe?) and then the next Thursday (15/09/05) in the next cell, then click those two cells and drag the handle, you will see sequential Thursdays to be entered in the cells you drag over.

That even works if you put in alternate Thursdays, when 2 or even three or more weeks is between.
Along with numerical dates, you could just as easily use Month-day values, like Sep 8 and Sep 15. When you drag out that handle you will see: Sep 22, Sep 29, Oct 6, Oct 13, etc.

Another point, you could use any particular day of one month, such as Sep 1, and Oct 1, then when you drag out the handle you will see: Nov 1, Dec 1, Jan 1, etc.

One last, and pretty interesting point (if you are still reading this, you must still be interested) is what if you enter in two cells Sep 30, and Oct 30. When you drag that out you would expect to see Nov 30, Dec 30, Jan 30, Feb 30, Mar 30...? (wait, there aren't 30 days in February - are there?).

Try it, and see what happens. Happy

- Collapse -
That was cute...
Sep 5, 2005 3:32PM PDT

...Chuck, especially the last one about February. This was certainly a much easier option. Happy filling to all the readers.

Krishnan