• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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!
1 Solution
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now