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 Date/ Time data (Excel 2007)

Discussion in 'Business Applications' started by mzalikhan, May 31, 2011.

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

    mzalikhan Thread Starter

    Joined:
    May 31, 2011
    Messages:
    36
    Hi everybody

    I have got an hourly meteorological data for 10 years. The problem is that some of the data entries are missing. e.g.

    23-April-2006 19:00
    23-April-2006 20:00
    23-April-2006 22:00
    23-April-2006 23:00

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

    23-April-2006 19:00
    23-April-2006 20:00
    23-April-2006 21:00
    23-April-2006 22:00
    23-April-2006 23:00

    Please also note that for some years, the data entries are for half past hours. e.g.

    31-Dec-2001 23:30
    01-Jan-2002 00:30
    01-Jan-2002 01:30
    01-Jan-2002 02:30

    Peace
    Muhammad Zeeshan
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,572
    First Name:
    Hans
    Hi Muhammad,
    Welcome to the forum.
    Let me see if I understand it correctly, you have a sheet with data and you need to insert blank rows for the missing hours per date.
    For the list with half hours I imagine the check for this will be the year value on or before 2001?
    Do you have a Date column and a Time column? if Yes which columns?
    Or
    Do you have a Date and time value in one column only? Which column is this?
    I would like to take a shot at it but then I would need this information.
    Peace to you too.
    Let me know.
     
  3. mzalikhan

    mzalikhan Thread Starter

    Joined:
    May 31, 2011
    Messages:
    36
    Hi Keebellah

    I am attaching the data samples. Please remember that these are just samples and due to file size limitation, i kept data minimum. The full data is for ten years for both Full hour and half hours.

    Actually 10 year data is recorded at different stations. Some stations recorded for half hour while others for full hours.

    Hope the attachments can speak better.

    Thanks and Peace
     

    Attached Files:

  4. Bradybunch

    Bradybunch

    Joined:
    Sep 10, 2009
    Messages:
    211
    I guess its not just as simple to add a new row where you need the data to be placed?
     
  5. mzalikhan

    mzalikhan Thread Starter

    Joined:
    May 31, 2011
    Messages:
    36
    @Bradybunch

    Hi

    I just want to put Date and time in column A (as it is already there in column A). Rest of the row would be blank.

    for example if it is like

    23-April-2006 20:00 23 43 343 4343
    23-April-2006 22:00 65 56 655 5656

    it should be converted to


    23-April-2006 20:00 23 43 343 4343
    23-April-2006 21:00
    23-April-2006 22:00 65 56 655 5656
    and so on (starting from 2000 to 2010)

    Please also note that some times data is missing for two or more consecutive hours (even for full days some time).

    Peace.
     
  6. Bradybunch

    Bradybunch

    Joined:
    Sep 10, 2009
    Messages:
    211
    You mean like this?

    What i did was copy the two columns into word, but paste as text only (In Office 2010 Word) bring the time back to the the date and put a space in. (Creates the space between the time and date, keeps it together), and copy and paste back into the spreasheet under the paste optio keep source formatting. Then puts it into your one column. And your data stays together.
     

    Attached Files:

  7. mzalikhan

    mzalikhan Thread Starter

    Joined:
    May 31, 2011
    Messages:
    36
    In the sample_hourly file that i attached, in A23 it should be 1/3/2004 7:00:00 PM. But its missing. Rather it comes 1/3/2004 8:00:00 PM right after 1/3/2004 6:00:00 PM.

    Similarly, between A37 and A38, there are some rows missing.

    What i want is to fill the column A as series (D-MON-YR HH:MM:SS).

    I hope this i explained better :)

    Peace
     
  8. Bradybunch

    Bradybunch

    Joined:
    Sep 10, 2009
    Messages:
    211
    Hmmm, well i just put the data in there randomly last time, but i put in there properly this time?
     

    Attached Files:

  9. Bradybunch

    Bradybunch

    Joined:
    Sep 10, 2009
    Messages:
    211
    Then just add new Rows in like the attahced?
     

    Attached Files:

  10. mzalikhan

    mzalikhan Thread Starter

    Joined:
    May 31, 2011
    Messages:
    36
    You mean add rows manually???
    Actually the the total data is slightly larger (years) so its next to impossible for me to put it manually.

    By the way you can change the 14-Jan-01 formate to 14-Jan-01 09:00 formate by simply

    Selecting the column>>>> Right click on the Top of column>>>> Formate Cell>>> Number (first Tab) and under category select time.

    You wont have to copy-paste in word for doing that.

    Peace :)
     
  11. Bradybunch

    Bradybunch

    Joined:
    Sep 10, 2009
    Messages:
    211
    Well yes i was thinkning manually...i guess you was not then :s
    Oh didnt know that haha, but does it keep your times by doing that?
     
  12. mzalikhan

    mzalikhan Thread Starter

    Joined:
    May 31, 2011
    Messages:
    36
    Displaying time is not an issue here. Issue is

    Inserting rows where hour is missing (e.g. Between 22 and 23 row in sample_hourly)
    putting the missing date and time in column A of the inserted row

    We would surely need some macro for doing that.

    Thanks anyway for the effort :)
    Peace
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,572
    First Name:
    Hans
    I'm picking up your files and will code the macro to do this.
    I'll check your data time format to keep it the same.
    I'll get back to you, just a little patience please
     
  14. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    10 years or hourly meteorological data? I think you need to consider a database and the automation factor not to mention, the amount of data, would be far more easily managed.
     
  15. mzalikhan

    mzalikhan Thread Starter

    Joined:
    May 31, 2011
    Messages:
    36
    @ Keebellah

    Sure I am waiting :)

    @ Rockn

    Well if you mean to say that its too huge data set to be dealt in excel (10 x365 x 24 entries), then i can divide it in 10 files (one per year) and recombine after processing.

    I am not really into data base or more complicated softwares than excel or SPSS, unless of course there is no other choice :)

    Peace
     
  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/999936

  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