On 5/5/2018 8:19 PM, GS wrote:
...

So then, when dates are imported/pasted into cells, Excel reads the
DateSerial and renders that in the system format. Now since the numeric
display has been ambiguous to many since Vista, my solution has been to use
textual formatting instead. (That's all I'm saying) So when I design
spreadsheets I deliberately use textual date formats so there's no
ambiguity (as expressed by the OP in this thread) as to what the date is!

It appears that in current versions you can't import two files with
conflicting definitions? I wasn't aware of that; hadn't really tested
presumed that if one had dates provided in a given format you could read them
as the system that generated them defined them.

_Way_ back, worked with a data-acq system that used dd\mm\yyyy and I surely
don't recall having an issue back then (this, of course, was in DOS/Win 3 era
thru _maybe_ W95).

I just tried to enter as a string '5/5/2000' into two cells and format it as
mm/dd/yyyy in one and dd/mm/yyyy in the other and couldn't succeed; the OS
setting as you say interferes and corrupts the one; MS thinks they know
better than the user it appears.

I don't recall that being the case in the days of that old system...or am I
just getting that senile; can't believe I wouldn't recall having to have had
to do a conversion outside for the end users (albeit I was using Matlab for
the work I did which reads the data and believes the user's intention is what
is to be, not what it thinks it _should_ be).

Anything we did pre-Vista is no longer valid in terms of numeric (05/05/2018)
input. It's not that Excel thinks it knows better, but rather that it reads
DateSerial values rather than display values when it comes to dates.

Where the problem lies for users is in [not] changing the way they enter dates
in terms of d/m or m/d, depending on which OS they're working in. I was forced
to figure this out after buying my 1st Win7 machine while still using my XP
unit as my main machine.

So docs done on pre-Vista systems would still show correct dates when opened in
the later OSs because regardless of how the system format displays, the
DateSerial dictates the actual date value!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 5/6/2018 7:37 PM, GS wrote:
....

So docs done on pre-Vista systems would still show correct dates when
opened in the later OSs because regardless of how the system format
displays, the DateSerial dictates the actual date value!

That's ok for stuff already _in_ MS product; what about the question of
importing data from external systems that may have different encoding
between them? Looks like that's broken to me...

--

On 5/6/2018 7:37 PM, GS wrote:
...

So docs done on pre-Vista systems would still show correct dates when
opened in the later OSs because regardless of how the system format
displays, the DateSerial dictates the actual date value!

That's ok for stuff already _in_ MS product; what about the question of
importing data from external systems that may have different encoding between
them? Looks like that's broken to me...

Hmm.., I guess you're talking about dbase date formats and AFAIK these also use
DateSerial; - but then that only applies to MS-based or Windows-based dbases. I
haven't run into any issues using generic dbases like SQLite, though, but then
never did the XP/Vista+ testing thing with that. I do know the dates are
correct in my stuff because I set the format to always use the 3-char month
abreviation. (Suggests date values univerally use DateSerials to be cross-OS
compatible!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 5/6/2018 8:03 PM, GS wrote:
On 5/6/2018 7:37 PM, GS wrote:
...

So docs done on pre-Vista systems would still show correct dates when
opened in the later OSs because regardless of how the system format
displays, the DateSerial dictates the actual date value!

That's ok for stuff already _in_ MS product; what about the question
of importing data from external systems that may have different
encoding between them?* Looks like that's broken to me...

Hmm.., I guess you're talking about dbase date formats and AFAIK these
also use DateSerial; - but then that only applies to MS-based or
Windows-based dbases. I haven't run into any issues using generic dbases
like SQLite, though, but then never did the XP/Vista+ testing thing with
that. I do know the dates are correct in my stuff because I set the
format to always use the 3-char month abreviation. (Suggests date values
univerally use DateSerials to be cross-OS compatible!)

No, the issue of text from two disparate systems; one encoded as
mm/dd/yyyy and the other dd/mm/yyyy. AFAICT, there's no way other than
to have to recode to match the system default to get the one not
matching to be interpreted correctly. That's just rude...

--

On 5/6/2018 8:03 PM, GS wrote:
On 5/6/2018 7:37 PM, GS wrote:
...

So docs done on pre-Vista systems would still show correct dates when
opened in the later OSs because regardless of how the system format
displays, the DateSerial dictates the actual date value!

That's ok for stuff already _in_ MS product; what about the question of
importing data from external systems that may have different encoding
between them?* Looks like that's broken to me...

Hmm.., I guess you're talking about dbase date formats and AFAIK these also
use DateSerial; - but then that only applies to MS-based or Windows-based
dbases. I haven't run into any issues using generic dbases like SQLite,
though, but then never did the XP/Vista+ testing thing with that. I do know
the dates are correct in my stuff because I set the format to always use
the 3-char month abreviation. (Suggests date values univerally use
DateSerials to be cross-OS compatible!)

No, the issue of text from two disparate systems; one encoded as mm/dd/yyyy
and the other dd/mm/yyyy. AFAICT, there's no way other than to have to
recode to match the system default to get the one not matching to be
interpreted correctly. That's just rude...

Actually, your example isn't textual dates; - they're numeric! This is where
the problem lies. A2 in my exercise is an example of textual date format; - it
doesn't matter what the system format is because that format will ALWAYS
display correctly.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 5/7/2018 2:14 AM, GS wrote:
Actually, your example isn't textual dates; - they're numeric! This is
where the problem lies. A2 in my exercise is an example of textual date
format; - it doesn't matter what the system format is because that
format will ALWAYS display correctly.

It seems like the text ' confused Excel and my little-used Excel
"skills" led me down the primrose path...I forgot that 5/5 isn't
interpreted as division if don't use the preceding '=' so was trying too
hard to force interpretation as date.

Using just 5/6 or whatever does get interpreted correctly and one can
use a custom format of d/m/yy _or_ m/d/yy OK and mix them; all is well
after all...sorry for my miscue on the data entry.

The point still is, though, that your example all starts with the date
form being known a priori and all the example does is use a
non-ambiguous visual format to display the content.

There still is no way to determine which of two ambiguous date forms
from another system _AS THE TEXT DATE STRING_ is which from the string
format alone; and it still isn't totally clear for OP's problem after
the explanation whether he has the required information at the point
he's trying to solve the problem or not.

What I've discovered is that you can still manually force the cell
format to interpret the external input correctly by applying the
appropriate format but the initial input will be interpreted based on
the system setting. I'm used to being able to use MATLAB input forms in
which I can specifically define that the input format is what I want
irrespective of the system settings.

--

On 5/7/2018 2:14 AM, GS wrote:
Actually, your example isn't textual dates; - they're numeric! This is
where the problem lies. A2 in my exercise is an example of textual date
format; - it doesn't matter what the system format is because that format
will ALWAYS display correctly.

It seems like the text ' confused Excel and my little-used Excel "skills" led
me down the primrose path...I forgot that 5/5 isn't interpreted as division
if don't use the preceding '=' so was trying too hard to force interpretation
as date.

Using just 5/6 or whatever does get interpreted correctly and one can use a
custom format of d/m/yy _or_ m/d/yy OK and mix them; all is well after
all...sorry for my miscue on the data entry.

The point still is, though, that your example all starts with the date form
being known a priori and all the example does is use a non-ambiguous visual
format to display the content.

There still is no way to determine which of two ambiguous date forms from
another system _AS THE TEXT DATE STRING_ is which from the string format
alone; and it still isn't totally clear for OP's problem after the
explanation whether he has the required information at the point he's trying
to solve the problem or not.

What I've discovered is that you can still manually force the cell format to
interpret the external input correctly by applying the appropriate format but
the initial input will be interpreted based on the system setting. I'm used
to being able to use MATLAB input forms in which I can specifically define
that the input format is what I want irrespective of the system settings.

If the source data is indeed StringText then you're at the mercy of Excel
interpreting as per system date format and the resulting ambiguity. Using
textual date formats ("May 05, 2018") will ALWAYS display correctly because
Excel will indeed treat them as text. (ergo not useable in formulas by direct
ref to the cell)

If the source data uses date formats then what displays is a DateSerial in the
chosen format. In this case Excel will use the DateSerial and render it in the
format of its target cell. All is well!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

There still is no way to determine which of two ambiguous date forms from
another system _AS THE TEXT DATE STRING_ is which from the string format
alone; and it still isn't totally clear for OP's problem after the
explanation whether he has the required information at the point he's trying
to solve the problem or not.

The OP's Q:
"Is there anyway to *check with VBA* if a date is entered as DD-MM-YY or
MM-DD-YY? How can I tell *using VBA* if 4-5-18 is actually 4th of May of
Fifth of April?"

Auric's reply answers this accurately!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion