gideonjones
asked on
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!
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!
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.
If the above assumptions are correct, I could write you a quick macro that could do these conversions for you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
Thanks,
Brian.