importing ambiguous dates

[Jey]

HI,

I'm programming tools (in VBA) for importing GPS collar data etc from excel
to access. So far the tool:
1) opens the excel workbook,
2) loops through all of the data to make sure it's valid for the fields it's
destined to go into
3) copies it as values to a new worksheet (many fields are calculated)
4) defines a named region to encompas the data
5) imports it using DoCmd.TransferSpreadsheet ac Import...

My question is in regards to dates. I check for valid dates (if IsDate(....)
then blah blah blah...) which is all well and good, but I also need to find
out if the date is in dd/mm/yyyy or mm/dd/yyyy format...

The destination date field displays as dd/mm/yyyy. If the imported date is
already dd/mm/yyyy, or is written long form (ex either July 4, 2008 or 4
july, 2008), or is mm/dd/yyyy where the day is > 12 then there are no
problems.

The problem occurs when the incoming date is mm/dd/yyyy and the day/month is
ambinguous... the day & month don't get switched, and july 4th becomes april
7th.

I've got warning to the user that they must enter dates as dd/mm/yyyy, and
warnings that they should check the records created inthe database... but for
the GPS data imports in particular the incoming excel worksheets can have
10's of thousands of records. Too many to manually check. I'd rather make it
foolproof!

Is there a way of checking for dd/mm/yyyy vs mm/dd/yyyy when the dates could
be coming from any manner of excel cell formats (text, date, general, who
knows what else). Is there a way of forcing same data into dd/mm/yyyy before
importing (in excel) or during the import process?

Thanks in advance for any suggestions!

Jey

 

[Graham Mandeno]

Hi Jey

There is no way to check whether a user entering 04/07/2008 intended this to
mean 4-Jul-2008 or 7-Apr-2008.

The best way to ensure error-free data is to use an unambiguous date format
at your data entry stage (in this case, in your spreadsheet).

If the date column is formatted as dd-mmm-yyyy and the user enters 9/11,
then as soon as s/he leaves the cell the date will be displayed either as
9-Nov-2008 or 11-Sep-2008. If this was not the intention then the mistake
will (hopefully) be spotted and corrected at the time.

 

[Jey]

I was afraid you'd say that. The problem is that the excel sheet is more of a
data 'gathering' sheet than a data 'entry' sheet... most of the data will
actually be pasted into it rather that enterd directly. This tends to
overwrite any kind of formatting that I'd like to enforce on it!

I may have to accept that all I can do is warn the users on the danger of
mm/dd dates!

Jey

Hi Jey

There is no way to check whether a user entering 04/07/2008 intended this to
mean 4-Jul-2008 or 7-Apr-2008.

The best way to ensure error-free data is to use an unambiguous date format
at your data entry stage (in this case, in your spreadsheet).

If the date column is formatted as dd-mmm-yyyy and the user enters 9/11,
then as soon as s/he leaves the cell the date will be displayed either as
9-Nov-2008 or 11-Sep-2008. If this was not the intention then the mistake
will (hopefully) be spotted and corrected at the time.


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

HI,

I'm programming tools (in VBA) for importing GPS collar data etc from
excel
to access. So far the tool:
1) opens the excel workbook,
2) loops through all of the data to make sure it's valid for the fields
it's
destined to go into
3) copies it as values to a new worksheet (many fields are calculated)
4) defines a named region to encompas the data
5) imports it using DoCmd.TransferSpreadsheet ac Import...

My question is in regards to dates. I check for valid dates (if
IsDate(....)
then blah blah blah...) which is all well and good, but I also need to
find
out if the date is in dd/mm/yyyy or mm/dd/yyyy format...

The destination date field displays as dd/mm/yyyy. If the imported date is
already dd/mm/yyyy, or is written long form (ex either July 4, 2008 or 4
july, 2008), or is mm/dd/yyyy where the day is > 12 then there are no
problems.

The problem occurs when the incoming date is mm/dd/yyyy and the day/month
is
ambinguous... the day & month don't get switched, and july 4th becomes
april
7th.

I've got warning to the user that they must enter dates as dd/mm/yyyy, and
warnings that they should check the records created inthe database... but
for
the GPS data imports in particular the incoming excel worksheets can have
10's of thousands of records. Too many to manually check. I'd rather make
it
foolproof!

Is there a way of checking for dd/mm/yyyy vs mm/dd/yyyy when the dates
could
be coming from any manner of excel cell formats (text, date, general, who
knows what else). Is there a way of forcing same data into dd/mm/yyyy
before
importing (in excel) or during the import process?

Thanks in advance for any suggestions!

Jey

 

[John W. Vinson]

Is there a way of checking for dd/mm/yyyy vs mm/dd/yyyy

Not even conceptually.

If you have a text value 04/07/2008 and that's all the information that you
have, how could you POSSIBLY determine which was intended?

 

[Jey]

No need to raise your voice, it was a legitimate question. Graham already
indicated that there is no way to check for which format was intended, and
provided helpful options as well.
As for what is 'conceptually possible', Einstein had a lot of ideas that
were outside of the concepts of physics in his day. I'm no Einstein, and so I
ask for the opinion of those who may have a broader understanding of the
subject since I may have missed something obvious (it's happened before!).
Thank you for your input into this discussion,
Jey