+ Reply to Thread
Results 1 to 14 of 14

Insert Missing Hours

  1. #1
    Registered User
    Join Date
    11-28-2012
    Location
    Newfoundland
    MS-Off Ver
    Excel 2010
    Posts
    21

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    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. #3
    Registered User
    Join Date
    11-28-2012
    Location
    Newfoundland
    MS-Off Ver
    Excel 2010
    Posts
    21

    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. #4
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office XP to Office 2016
    Posts
    1,715

    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
    Teach me Excel VBA

  5. #5
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office XP to Office 2016
    Posts
    1,715

    Re: Insert Missing Hours

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

  6. #6
    Registered User
    Join Date
    11-28-2012
    Location
    Newfoundland
    MS-Off Ver
    Excel 2010
    Posts
    21

    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. #7
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office XP to Office 2016
    Posts
    1,715

    Re: Insert Missing Hours

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

  8. #8
    Registered User
    Join Date
    11-28-2012
    Location
    Newfoundland
    MS-Off Ver
    Excel 2010
    Posts
    21

    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. #9
    Registered User
    Join Date
    11-28-2012
    Location
    Newfoundland
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Insert Missing Hours

    Quote Originally Posted by Pete_UK View Post
    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. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,645

    Re: Insert Missing Hours

    Sheet 1 is Data

    Sheet 2 is Results

    See attachment.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    11-28-2012
    Location
    Newfoundland
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Insert Missing Hours

    Quote Originally Posted by bebo021999 View Post
    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. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,645

    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. #13
    Registered User
    Join Date
    11-28-2012
    Location
    Newfoundland
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Insert Missing Hours

    Quote Originally Posted by bebo021999 View Post
    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.
    Attached Files Attached Files

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,936

    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: copy to clipboard
    Please Login or Register  to view this content.
    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Compare two columns on separate sheet, if missing, insert missing data
    By lilvictorians in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 04-03-2019, 11:44 PM
  2. Insert missing dates
    By jmiller7 in forum Excel General
    Replies: 7
    Last Post: 10-19-2014, 09:40 PM
  3. Excel Macro to find missing dates and insert missing date as blank row
    By JevaMarie in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-26-2013, 11:08 AM
  4. Missing Insert table row below
    By noctornus in forum Excel General
    Replies: 1
    Last Post: 02-27-2013, 01:58 PM
  5. [SOLVED] Insert row when a row in the sequence is missing
    By mhelmers in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-29-2013, 10:58 AM
  6. How to find the missing row with certain value then insert new row
    By tuongtu3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-11-2012, 02:24 AM
  7. [SOLVED] Insert Missing Dates
    By Sal1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-07-2012, 01:25 AM

Bookmarks

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