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?

2 Answers 2


The fill handle works fine for me:
enter image description here

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):
enter image description here

  • @lan, I did this too, (by filling down), but the next datetime after 8/1/13 23:00 is 8/2/13 0:00. How did you get the above funky result? Jul 4, 2017 at 1:09
  • 5
    @Bandersnatch "my dates are formatted as DD/MM/YY". Obviously yours are set to MM/DD/YY.
    – Ian
    Jul 4, 2017 at 1:16
  • 1
    Ohhhhhh. :-). Seems obvious with hindsight. Good answer. Jul 4, 2017 at 2:12

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”

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.