# Insert Missing Hours

1. ## Insert Missing Hours

Hello,

I'm hoping my problem is easy to solve. I've got one years worth of weather data and it can be recorded at frequencies of 1 hour, 3 hours, 6 hours or more. What I need to do is fill in missing hours. So, if I have a row that has 3 AM and the next one is 6 AM, is there an easy way of inserting 4 AM and 5 AM in between? I've attached a sample file.

Also, on a similar topic, if I have times that have a period in them instead of a colon, what is the easiest way to convert them; if I do a simple replace, it changes the values because it thinks it was decimal hours; if I change the format, that doesn't work either.

Thanks for any help.

2. ## Re: Insert Missing Hours

If you insert the missing hours, do you also want values for wind direction and speed to be filled, and if so how should they be derived - averages from the before and after values?

Pete

3. ## Re: Insert Missing Hours

Thanks for the reply. Sorry, I should've said that. I just want those rows to be blank except for the date/time, so the other columns would be empty.

4. ## Re: Insert Missing Hours

type 1 in G2
Type 2 in G3
select G2:G3 and fill down
Copy the selection and paste in G4177
then go to G8352 and paste the same there as well
Select G2 and sort A to Z
This process will insert two rows under each record.
In cell H3 paste this formula and copy down
=IF(A2=TIME(3,0,0),A2+TIME(1,0,0),"")
in cell I4 paste this formula and copy down
=IF(A2=TIME(3,0,0),A2+TIME(2,0,0),"")
In cell j2 type this and copy down
=IF(A2<>"",A2,H2)
In cell K2 and copy down

=IF(A2<>"",A2,I2)
Now copy full range of K column and paste as values in A2
Delete columns G to K
Now copy column A from A2 to al the way down to the last record
Paste in G2
With the G Column Selected with the paste operation
Go to Home tab
Go to find and select
Go to special
check the "Constant" Make sure only text is check as sub category
this will select all the blank cells in G column
right click on any of selected cell and click delete and delete entire row
Done

5. ## Re: Insert Missing Hours

If the attachment is your original data then I have attached back the operated workbook here.

6. ## Re: Insert Missing Hours

Thanks for the reply and for doing this in the worksheet. Unfortunately, it is not putting in all of the missing hours. For instance on the first day (January 1, 2016), it has two for 0:00 (12 AM), but it doesn't have 1:00, 2:00, 7:00 or 8:00.

7. ## Re: Insert Missing Hours

Ah Ok....I thought you want to insert only 4 AM and 5 AM.will look into.

8. ## Re: Insert Missing Hours

Hello, I'm just wondering if you are able to help me with this problem to insert all of the missing hours?

9. ## Re: Insert Missing Hours

Originally Posted by Pete_UK
If you insert the missing hours, do you also want values for wind direction and speed to be filled, and if so how should they be derived - averages from the before and after values?

Pete
Are you able to assist with this problem?

10. ## Re: Insert Missing Hours

Sheet 1 is Data

Sheet 2 is Results

See attachment.

11. ## Re: Insert Missing Hours

Originally Posted by bebo021999
Sheet 1 is Data

Sheet 2 is Results

See attachment.
Thanks. Is there a reason that the wind speed/direction is only copied through to the 2100 on January 4?

12. ## Re: Insert Missing Hours

Change the range from \$B\$2:\$B\$41

to \$B\$2:\$B\$4176

=IFERROR(LOOKUP(2,1/(ROUND(DATE(DATA!\$B\$2:\$B\$4176,DATA!\$C\$2:\$C\$4176,DATA!\$D\$2:\$D\$4176)+DATA!\$A\$2:\$A\$4176,9)=ROUND(\$A2,9)),DATA!F\$2:F\$4176),"") to

13. ## Re: Insert Missing Hours

Originally Posted by bebo021999
Change the range from \$B\$2:\$B\$41

to \$B\$2:\$B\$4176

=IFERROR(LOOKUP(2,1/(ROUND(DATE(DATA!\$B\$2:\$B\$4176,DATA!\$C\$2:\$C\$4176,DATA!\$D\$2:\$D\$4176)+DATA!\$A\$2:\$A\$4176,9)=ROUND(\$A2,9)),DATA!F\$2:F\$4176),"") to
I did that, but it's not copying over all of the wind data. It's missing a lot of the hours for some reason and only putting in data for the first few weeks of January (some missing data there too). I'm attaching the spreadsheet with that formula copied down to the end.

14. ## Re: Insert Missing Hours

I believe that the attached file has all of the wind speed and wind direction values filled in, although admittedly I didn't do an exhaustive search. The main formula for wind speed is:
Formula:
The wind direction formula is very similar.
Note: Column K is a helper column which may be hidden for aesthetic purposes.
Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1