Autofill excel with an hourly breakdown for an extended amount of time

Question

I am trying to set up an excel document with an hourly breakdown for an entire year. However, I cannot figure out how to do this without having to hand enter the date/time, due to the day needing to be repeated 24 times before moving onto the next day and therefore does not follow a pattern that excel will recognize and autopopulate.

I.e. I nee something that looks like this:

08/01/2013 00:00

08/01/2013 01:00

08/01/2013 02:00

. . .

08/01/2013 23:00

08/02/2013 00:00

08/02/2013 01:00

and so on...

In essence there should be 8,760 cells (365 days x 24 hours), which I am trying to avoid having to type in by hand.

Does anyone know a command, tool, or preset up workbook I can use to do this?

Answer 1

The fill handle works fine for me:

Once you have selected enough cells for Excel to determine the pattern, dragging the fill handle will continue the pattern for you. In this case, it knows to keep adding one hour. Extending it out works for the next day (my dates are formatted as DD/MM/YY):

Answer 2

Here's another way to achieve the same thing, for comparison. It uses a formula, and “Fill Down” instead of “Autofill”.

1. Enter “08/01/2013 00:00” into cell A1 (assuming your date format is mm/dd/yyyy)
2. Enter =$A$1+(ROW()-1)/24 into cell A2
3. Use the “Format Painter” to copy the format from cell A1 to cell A2
4. With A2 selected, in the “Name Box” (to the left of the formula bar) type A8760 and press Shift+Enter, which selects all the cells from A2 to A8760
5. Press Ctrl+D which copies the formula from A2 to all the selected cells
6. If required, right-click and “Copy”, then right-click and “Paste Values”