1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Inserting Rows for Missing time/date data

Discussion in 'Business Applications' started by Vidhesh, Feb 13, 2019.

Thread Status:
Not open for further replies.
Advertisement
  1. Vidhesh

    Vidhesh Thread Starter

    Joined:
    Feb 13, 2019
    Messages:
    41
    I have got an hourly meteorological data for 10 years. The problem is that some of the data entries are missing. for ex

    01-07-2006 6:30
    01-07-2006 8:30
    01-07-2006 14:30
    01-07-2006 16:30

    I need to insert the rows for missing hours. Rest of the columns in the inserted row are needed to be blank. e.g.


    01-07-2006 6:30
    01-07-2006 7:30
    01-07-2006 8:30
    01-07-2006 9:30
    ..... and so on.

    I have been struggling with this data for weeks, I have attached a file below.
     

    Attached Files:

  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I think this does what you want, press Ctrl + q to run the macro VBA.
     

    Attached Files:

  3. Vidhesh

    Vidhesh Thread Starter

    Joined:
    Feb 13, 2019
    Messages:
    41
    OBP you are a life saver, I can't thank you enough. Thank You very much.
     
  4. Vidhesh

    Vidhesh Thread Starter

    Joined:
    Feb 13, 2019
    Messages:
    41
    OBP there's an issue with the data, we have to arrange the data according to hours, so that 24 hours in a day are complete. What's happening right now is that 24 hours in the data are continuous for ex from 0-23 but the date changes before the 24 hours are complete. I have attached a file below for reference, in the file time starts from 5:30 till 4:30 and then the date should change but what's happened is that date has changed before which I have highlighted in the data. Please take a look at it. Thanks in advance.
     

    Attached Files:

  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If you are talking about the section in Red, then that is like that in the original data ie column H does not match column F. This happens in lots of places.
    It does not come from the VBA code which concentrates on columns E & F.
    So should the code concentrate on column H?
     
  6. Vidhesh

    Vidhesh Thread Starter

    Joined:
    Feb 13, 2019
    Messages:
    41
    Actually the column E corresponds to column F and the G corresponds to column H. So when I will use the data to calculate the average of for ex Air temperature in column (I) then I will drag the values from 5:30 in column G till 4:30 to get 24 values ie values for a day. But what will happen is that the date will not correspond to 24 values and the date changes in column H before it reaches 24 values ie completing a day. So if you can make the code somewhat the values of E correspond to F and G corresponds to H. I will be really grateful if you could help me out with this.
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you clarify the date format that you are using?
    Is it
    DD/MM/YYYY
    or
    MM/DD/YYYY
    And is it the same in both columns?
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I am familiar with Temperature data, being interested in Climate Change, so I am wondering how you are handling the missing data when calculating your daily average?
     
  9. Vidhesh

    Vidhesh Thread Starter

    Joined:
    Feb 13, 2019
    Messages:
    41
    The date format is MM/DD/YYYY, yes its same in both columns.
     
  10. Vidhesh

    Vidhesh Thread Starter

    Joined:
    Feb 13, 2019
    Messages:
    41
    If the missing data is 3-4 values then it's fine, I take the average from the available values for daily average. But if the data is missing for large number of hours then I extrapolate the values based on the previous average, and I do that manually.
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, that is giving some confusion as my National default for dates is DD/MM/YYYY, so the VBA will add a day to the wrong point ie a month instead of a day.
    Will it work OK for you?
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The averages could also be calculated by VBA as well of course.
     
  13. Vidhesh

    Vidhesh Thread Starter

    Joined:
    Feb 13, 2019
    Messages:
    41
    Can you set the format for dates as DD/MM/YYYY for all the values in my data. In that way it will be uniform.
     
  14. Vidhesh

    Vidhesh Thread Starter

    Joined:
    Feb 13, 2019
    Messages:
    41
    I take out the average manually by dragging the 24 values and then applying the formula as I am unequipped in VBA, could you attach two files one with average and one without it?
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I will see if I can do a Custom Date for those columns.
    No, that doesn't work it gets confused by the 01/08/2006.
    I think it would be best that you set the columns in your format (it has to be the column to format the Inserted rows) or you format them afterwards.
    Provided that you do so before running the code it should be OK.
    So give this a try.
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1223053

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice