Solved: Inserting rows for Missing Date/ Time data (Excel 2007)

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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.
 

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
 

Attachments

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

Attachments

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
 

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 :)
 
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?
 

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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
 
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.
 

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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top