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

On 5/7/2018 12:06 PM, GS wrote:
....

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)
....

Yes, but sometimes with foreign systems one doesn't have the ability to
change the output form...sad, but ime, more often than one would think,
still all too true for specialty systems from a hardware vendor or the
like that are just simple prepackaged demos of how to use the system but
also ime, probably 70-80% of the clients would never have written a
better tool for their purpose but instead just make do with the toy
sample from the vendor and live with the warts.

Been burnt too many times.... :(

--

On 8-May-2018 6:53 am, dpb wrote:
On 5/7/2018 12:06 PM, GS wrote:
...

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)
...

Yes, but sometimes with foreign systems one doesn't have the ability
to change the output form...sad, but ime, more often than one would
think, still all too true for specialty systems from a hardware vendor
or the like that are just simple prepackaged demos of how to use the
system but also ime, probably 70-80% of the clients would never have
written a better tool for their purpose but instead just make do with
the toy sample from the vendor and live with the warts.

Been burnt too many times.... :(

--


slightly off topic..

I've been following this thread and have learned things that will help
when I encounter date problems in excel and VBA which I often do.

But what amazes me is that we persist in using these two date formats -
d/m/y and m/d/y. It seems absurd we accept conventions that are
blatantly so ambiguous! And, especially, their continuing use by
software engineers who are expected to think logically. I'm always
coming across applications or data where I have to struggle to work out
which is being used.

d/m/y and m/d/y are not formally internationally accepted date formats
and I'm surprised that there hasn't been a trend towards a more rational
format, such as yyyy-mmm-dd. The very least is a format where the
elements have increasing or decreasing significance - unlike m/d/y which
is neither.

On 5/7/2018 3:19 PM, malone wrote:
....

But what amazes me is that we persist in using these two date formats -
d/m/y and m/d/y. It seems absurd we accept conventions that are
blatantly so ambiguous! And, especially, their continuing use by
software engineers who are expected to think logically. I'm always
coming across applications or data where I have to struggle to work out
which is being used.

d/m/y and m/d/y are not formally internationally accepted date formats
and I'm surprised that there hasn't been a trend towards a more rational
format, such as yyyy-mmm-dd. The very least is a format where the
elements have increasing or decreasing significance - unlike m/d/y which
is neither.

No, but we 'Murricuns aren't much given to having others tell us what to
do; note the similar reluctance for widespread adoption of metric for
common use; after nearly 40 yr since was a widespread attempt to
enforce, it's still gotten essentially nowhere outside industrial and
scientific circles and I suspect another 40 will be about the same.

Date convention in US is a similarly ingrown habit; that there are
coding standards is pretty-much immaterial to the casual user and I'd
venture 70-80% of the spreadsheets in use are done by just that type of
individual; may be pretty adept at the usage of Excel interface and all,
but little or no actual coding training so they continue to just use
everyday convention.

As noted in an earlier response, in a 40-yr+/- consulting career in
mostly data acq and instrumentation for power utilities I saw a hundred
or more data acq systems put together from probably over half that many
vendors and every conceivable form and some that were truly incredible
for encoding date and time was in the sample set I think. As noted,
even vendors would (and I'm sure still do) provide sample code most
probably put together by the summer intern that would think something as
sophisticated of deliberately using the sequence order of d/m/y as
almost revolutionary and, probably, "European". Teaching those folks
about serial numbers is a struggle that one rarely wins.

As noted, Matlab has a class |datetime| and has enhanced the C i/o
format string to read date strings such that one can on a case-by-case
basis handle it on input. MS tends to also be heavy-handed in they code
as if everything is MS-consistent and leave it up to the user to conform
or write the glue to interact.

--

On 8-May-2018 6:53 am, dpb wrote:
On 5/7/2018 12:06 PM, GS wrote:
...

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)
...

Yes, but sometimes with foreign systems one doesn't have the ability to
change the output form...sad, but ime, more often than one would think,
still all too true for specialty systems from a hardware vendor or the like
that are just simple prepackaged demos of how to use the system but also
ime, probably 70-80% of the clients would never have written a better tool
for their purpose but instead just make do with the toy sample from the
vendor and live with the warts.

Been burnt too many times.... :(

--


slightly off topic..

I've been following this thread and have learned things that will help when I
encounter date problems in excel and VBA which I often do.

But what amazes me is that we persist in using these two date formats - d/m/y
and m/d/y. It seems absurd we accept conventions that are blatantly so
ambiguous! And, especially, their continuing use by software engineers who
are expected to think logically. I'm always coming across applications or
data where I have to struggle to work out which is being used.

d/m/y and m/d/y are not formally internationally accepted date formats and
I'm surprised that there hasn't been a trend towards a more rational format,
such as yyyy-mmm-dd. The very least is a format where the elements have
increasing or decreasing significance - unlike m/d/y which is neither.

Well stated!
My preference is yyyy-mmm-dd (1st) or mmm-dd-yyyy (2nd) for legal purposes,
otherwise mmm-dd or dd-mmm for storing date values. In all cases the 3 char
month abbreviation is used. Using yyyy in the 1st element position (yyyy-mm-dd)
just makes sense for sorting purposes, IMO:)

Thankfully, most development languages use DateSerial and offer text formatting
options as to how the developer wants that to be displayed. But as you state, I
can't understand why they persist to use an ambiguous format; - after all, it's
not like they don't know how systems they develop for work!

--
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 4:38 PM, GS wrote:
"...most development languages use DateSerial"

Well, let's say most have a library available for the developer _to_
use. :)

MS, of course, has a problem in that they think 1900 was a leap year so
are off by one compared to those who don't (correctly). I don't know
which way the C library work or POSIX or whether there's an
interpretation on how should work.

--

On 5/7/2018 4:38 PM, GS wrote:
"...most development languages use DateSerial"

Well, let's say most have a library available for the developer _to_ use. :)

Actually, for VB[A] it's called a runtime.dll which is, as noted, the dev app's
library. The developer has no control over the functions or methods the lib
exposes, though.

MS, of course, has a problem in that they think 1900 was a leap year so are
off by one compared to those who don't (correctly). I don't know which way
the C library work or POSIX or whether there's an interpretation on how
should work.

Most langs use DateSerial for date values since this has more-or-less been
adopted as a 'global' standard so 'regions' will capture the real value in
local formats.

I'm thinking, though, that terminology and familiarity with processes has
played a role in any misunderstandings that prevailed here. Entering 05/07/2018
into a cell in Excel (or any other spreadsheet) is raw data not text. Entering
that same data into a grid control is both raw data and text.

Don't know how your MatLab takes values but your dialog here suggests it
accepts string values that it uses to process whatever, OR interprets/treats
entered values as string data. Does this mean what gets entered is wrapped in
""? (You could enlighten me further on this, if you would please.)

--
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

On 5/7/2018 12:34 PM, GS wrote:
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!

That's true and acknowledged. What we don't know is whether the correct
answer to the question asked is the answer to the underlying problem or
not; insufficient data to tell.

What the answer is is what Excel thinks the data are and how will be
exported; what we don't know is whether that is the same as what the
foreign system thinks they are for the two cases--my gut feeling is
"there be dragons!" but don't know for sure.

--

On 5/7/2018 12:34 PM, GS wrote:
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!

That's true and acknowledged. What we don't know is whether the correct
answer to the question asked is the answer to the underlying problem or not;
insufficient data to tell.

What the answer is is what Excel thinks the data are and how will be
exported; what we don't know is whether that is the same as what the foreign
system thinks they are for the two cases--my gut feeling is "there be
dragons!" but don't know for sure.

Agreed!

--
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 4:40 PM, GS wrote:
....

What the answer is is what Excel thinks the data are and how will be
exported; what we don't know is whether that is the same as what the
foreign system thinks they are for the two cases--my gut feeling is
"there be dragons!" but don't know for sure.

Agreed!

Ah! We finally(!) got to the point was trying to make... :)

There's "knowing" and then there's "knowing"...and the two aren't always
necessarily the same.

--

On 5/7/2018 4:40 PM, GS wrote:
...

What the answer is is what Excel thinks the data are and how will be
exported; what we don't know is whether that is the same as what the
foreign system thinks they are for the two cases--my gut feeling is "there
be dragons!" but don't know for sure.

Agreed!

Ah! We finally(!) got to the point was trying to make... :)

There's "knowing" and then there's "knowing"...and the two aren't always
necessarily the same.

Very true:)

--
Garry

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