How to query date data in various forms?

Hi Experts,

I have a large excel database of over 7000 events. Around half of them have schedule information in their row - that is to say in the column that shows when it is happening they have something in there.

The problem is that this data has been input in a variety of ways - some of them have 05/05/2012 others have "June - October", others have Saturday, 6th march 2012 - Sunday the 7th of March 2012.

I am looking eventually for a way to be able to query this data in a useful way - i.e. say "what events are ongoing on date X", and the first step to that I think must be to get all the dates in the same form.... this seems like it is impossible to do automatically or with any function excel knows... but I thought it might be worth asking the experts in case they had any wizardry to achieve this seemingly impossible task?!! Thanks!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi, gideonjones.

Can you give us a set of rules for converting dates? Possible ones might be...

- For a single valid date (e.g. 05/05/2012), the date is both the start and end date.

 - Where the year is missing, infer it from the preceding/following entry. (This assumes that the events are in date sequence. If not, then how is year to be identified?)

- Where the day is missing then assume the 1st of the month for the start date and the last day of the month for the end date.

Also, are there any other date formats?

Based on your examples, it seems the month is always specified, by name ("march")  or numerically (05). The day number is always specified (6th, 05, etc).

If the above assumptions are correct, I could write you a quick macro that could do these conversions for you.
Glenn RayExcel VBA DeveloperCommented:
To make this flexible, one might want to do the following:
1) Create some kind of rule table that specifies how different types of data is to be interpreted.
2) Create a new column - preferably adjacent to the original data - where the interpreted dates can be inserted.

The latter step would allow you to refine any automated results as best fits your needs.

Given your examples, there are several possible issues to address:

05/05/2012  - If this is visible, then it's already been converted to an Excel date, BUT, there's no way to know if this is mm/dd/yyyy or dd/mm/yyyy.  In this case, it doesn't matter, but what if the result were 03/09/2012?  Is that March 9, 2012 or September 3, 2012?

"June - October" - What date would be appropriate here?  June 1 of the current year or October 31 of the current year?  You'd have to decide, hence a rule table.

"Saturday, 6th march 2012" - This is clear and would be easy enough to parse and translate (i.e., the string begins with a named day of the week)

"Sunday the 7th of March 2012" - This is also clear and could be handled like the previous example.

I'll work up an example workbook and macro and see if can get you started.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.