Adding rows of missing dates and times

Hello,

I have several spreadsheets containing temperature data back to 2001. My most recent data is complete, and temperature samples are taken every 5 minutes. However, the older samples were taken every 10 minutes and some samples are missing. An example of one of my old spreadsheets is below:

30/09/2001 13:00 15.0
30/09/2001 13:10 15.0
30/09/2001 14:10 14.5
30/09/2001 14:20 14.5
30/09/2001 14:30 14.5

In order to have spreadsheets that contain, I want to add in the missing dates, and put a blank in the temperature column. Thus, the example above would be corrected like this:

30/09/2001 13:00 15.0
30/09/2001 13:05
30/09/2001 13:10 15.0
30/09/2001 13:15
30/09/2001 13:20
30/09/2001 13:25
30/09/2001 13:30
30/09/2001 13:35
30/09/2001 13:40
30/09/2001 13:45
30/09/2001 13:50
30/09/2001 13:55
30/09/2001 14:00
30/09/2001 14:05
30/09/2001 14:10 14.5
30/09/2001 14:15
30/09/2001 14:20 14.5
30/09/2001 14:25
30/09/2001 14:30 14.5

Obviously the last date in each spreadsheet would have to be 31/12 (and in this case, 31/12/2001).

I tried a macro I found online that almost worked, but I couldn't work out how to customise it to use it for my data. Could anyone help me out with a macro?

Thank you very much in advance.

Hi, thanks very much for that, it's excellent!

I forgot some of my data actually has some extra columns (for things like wind speed, rainfall, etc) and some of the data goes up to (and including) Column K.

Would it be possible for you to please show me how to modify the macro to incorporate data up to Column K?

Sorry for not realising this before. Apart from this, the macro's spot on - thanks again!

I managed to get it working (I think) but it just has a limited column of dates on the left, along with blank columns to the right which have one of the temperature readings as the headers (example below). Also something funny has happened with the dates. I'm sure it's me who's doing something wrong!

..............................2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8
00/01/1900 00:05
00/01/1900 00:10
00/01/1900 00:15
00/01/1900 00:20
00/01/1900 00:25
00/01/1900 00:30

Here it is.

The spreadsheet has an example of the weather data (stretching to column K) and then when I run the macro, it does the strange thing I described in my last post.

Thanks.

Hello

I am using a version of this very useful procedure, modified to go in hourly timesteps. It works very well, except that it does not change the date when crossing midnight. i.e. It will go 30/10/12 23:00 then 30/10/12 00:00 then 31/10/12 01:00. Of course, this means that the matching formula does not place the data in the correct place. When I type the formula directly into cells, it works perfectly and changes the date in the expected place. So it is something that the procedure is doing in VBA that stops this from working correctly and I can't for the life of me figure out what, which implies it is something very simple (usually the case!)

Can anybody help me out? Thanks a lot.

OK sorry, I will start again