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