Excel: Copy identical data sorted by date-time to future dates

Question

I apologize in advance if this has been answered before but I couldn't find anything using similar wording to my question.

I have an Excel sheet of one year of data, with a value for every 15 minutes.

I need to copy this same data down for another 20 years or so. Same values, but the date-time values need to increase appropriately.

Excerpted sample section of data:

11/15/2013         0             0             0
11/15/2013  0:15   0             0             0
11/15/2013  0:30   0             0             0
11/15/2013  0:45   0             0             0
11/15/2013  1:00   0             0             0
11/15/2013  1:15   0             0             0
     ︙            ︙            ︙            ︙
11/15/2013  6:45   0             0             0
11/15/2013  7:00   0             0             0
11/15/2013  7:15   0             0             0
11/15/2013  7:30   2.343333244   0.620000005   0
11/15/2013  7:45   24.77111053   9.632222176   0
11/15/2013  8:00   55.38555527   24.6744442    107.1141281
11/15/2013  8:15   99.72555542   42.45111084   263.5869751
     ︙            ︙            ︙            ︙
11/15/2013 16:00   267.5866699   115.3866653   565.7128296
11/15/2013 16:15   172.2244415   95.99111176   421.3774414
11/15/2013 16:30   47.3777771    29.69000053   270.9610291
11/15/2013 16:45   7.023333549   8.140000343   115.9676056
11/15/2013 17:00   0.004444445   1.917777777   0
11/15/2013 17:15   0             0             0
11/15/2013 17:30   0             0             0
11/15/2013 17:45   0             0             0
11/15/2013 18:00   0             0             0
     ︙            ︙            ︙            ︙
11/15/2013 22:30   0             0             0
11/15/2013 22:45   0             0             0
11/15/2013 23:00   0             0             0
11/15/2013 23:15   0             0             0
11/15/2013 23:30   0             0             0
11/15/2013 23:45   0             0             0

^{(Go to the edit history to see a full day’s worth of data.)}

Excel isn't particularly my strong suit. I've tried various copying options but can't seem to get anything that dynamically creates new dates in 15 minute intervals. Is this even possible? Should I just copy an entire year down and try to change the year for each year? (I can't even figure out how to do that, either.)

Answer 1

I wonder whether I’m missing the point of this question.  Copy a year’s worth of data (35040 rows -- 365×24×4) 20 times, post-dating the timestamp?  I might be able to give a better answer if I understood your actual objective better.

Well, here are a couple approaches to doing what you seem to be asking for:

• In row 35041, set

  • A35041 to =A1+365
  • B35041 to =B1
  • C35041 to =C1
  • D35041 to =D1

  and drag/fill down.  If desired, copy and paste values.

• Copy the block of 35040 rows (if Excel will let you) and paste it, starting at row 35041.  Then type “365” somewhere in the workbook (could be on another sheet) and copy it.  Then select the timestamp for 2014 (A35041:A70080) and do “Paste” → “Paste Special” → “Operation” → “Add”.

Either way, you’ll have to deal with leap years (February 29) somehow.