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?

Jan

On 5/4/2018 2:31 AM, wrote:
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?

If that's all you have in isolation you can't...if there are a string of
dates such that can find a value 12 in the (presumed) month field then
you can make the presumption that's days and the other must be months
but without some additional hints or such a specific day that can be
recognized there's just insufficient data to be unequivocal.

--

dpb wrote:

On 5/4/2018 2:31 AM, wrote:
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?

If that's all you have in isolation you can't...if there are a string of
dates such that can find a value 12 in the (presumed) month field then
you can make the presumption that's days and the other must be months
but without some additional hints or such a specific day that can be
recognized there's just insufficient data to be unequivocal.

On the other hand, if the cell is properly formatted as a date, you can check
the NumberFormat property:

Dim tmp As Variant
tmp = Split(ActiveCell.NumberFormat, "/")
If UBound(tmp) 0 Then
Select Case LCase(tmp(0))
Case "d", "dd", "ddd", "dddd"
'd/m/y
Case "m", "mm", "mmm", "mmmm", "mmmmm"
'm/d/y
Case Else
'not formatted as date
End Select
End If

--
- Were you looking for something?
- A sense of the miraculous in everyday life.

On 5/4/2018 10:55 AM, Auric__ wrote:
dpb wrote:

On 5/4/2018 2:31 AM, wrote:
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?

If that's all you have in isolation you can't...if there are a string of
dates such that can find a value 12 in the (presumed) month field then
you can make the presumption that's days and the other must be months
but without some additional hints or such a specific day that can be
recognized there's just insufficient data to be unequivocal.

On the other hand, if the cell is properly formatted as a date, you can check
the NumberFormat property:

Dim tmp As Variant
tmp = Split(ActiveCell.NumberFormat, "/")
If UBound(tmp) 0 Then
Select Case LCase(tmp(0))
Case "d", "dd", "ddd", "dddd"
'd/m/y
Case "m", "mm", "mmm", "mmmm", "mmmmm"
'm/d/y
Case Else
'not formatted as date
End Select
End If


But if the cell is formatted as Date and contains the data, then it will
already be interpreted as whichever and all need to do is =MONTH() or
=DAY() and inspect return value to know...

Didn't seem as that was the OP's question/problem at least way it came
across to my reading...guess we'll say if comes back to amplify.

--

On 5/4/2018 10:55 AM, Auric__ wrote:
dpb wrote:

On 5/4/2018 2:31 AM, wrote:
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?

If that's all you have in isolation you can't...if there are a string of
dates such that can find a value 12 in the (presumed) month field then
you can make the presumption that's days and the other must be months
but without some additional hints or such a specific day that can be
recognized there's just insufficient data to be unequivocal.

On the other hand, if the cell is properly formatted as a date, you can
check
the NumberFormat property:

Dim tmp As Variant
tmp = Split(ActiveCell.NumberFormat, "/")
If UBound(tmp) 0 Then
Select Case LCase(tmp(0))
Case "d", "dd", "ddd", "dddd"
'd/m/y
Case "m", "mm", "mmm", "mmmm", "mmmmm"
'm/d/y
Case Else
'not formatted as date
End Select
End If


But if the cell is formatted as Date and contains the data, then it will
already be interpreted as whichever and all need to do is =MONTH() or =DAY()
and inspect return value to know...

Didn't seem as that was the OP's question/problem at least way it came across
to my reading...guess we'll say if comes back to amplify.

Hmm.., I rather like Auric's simplified solution since it indeed does EXACTLY
what I interpret the OP is looking to accomplish.

Note also that Excel uses the 'system' date format unless set otherwise for
specific cells. For example, after XP the format order for d/m got switched.

--
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/4/2018 4:37 PM, GS wrote:
....

Hmm.., I rather like Auric's simplified solution since it indeed does
EXACTLY what I interpret the OP is looking to accomplish.
....

Well, it'll tell him what the cell is formatted as; whether that's what
the data was when entered isn't determinable from the string which is
where _I_ thought OP was coming from... :)

Once it's in the cell it can be either depending on the format; is that
correct or not is still indeterminate.

--

On 5/4/2018 4:37 PM, GS wrote:
...

Hmm.., I rather like Auric's simplified solution since it indeed does
EXACTLY what I interpret the OP is looking to accomplish.
...

Well, it'll tell him what the cell is formatted as; whether that's what the
data was when entered isn't determinable from the string which is where _I_
thought OP was coming from... :)

Once it's in the cell it can be either depending on the format; is that
correct or not is still indeterminate.

Date data will display as various values according to cell.NumberFormat for
Dates. If the cell containing a date is General format then the system date
format will display.

Ambiguities arise when *numeric representation* displays as opposed to *textual
representation* displaying. Auric's code determines whether the 1st element
represents d or m. Isn't this what the OP wants to figure out?

FWIW:
I prefer to use textual representation for dates as...

Where year is assumed to be current, May-4, 4-May
(ie: an accounting ledger/journal for a specific fiscal)

Where year is not assumed: May-4, 2018, May-4-2018, 4-May, 2018, ...

...where the NumberFormat is defined in Custom as "mmm-d" or as respective to
the desired display. This way, it doesn't matter what the system format is and
so there's no ambiguity whatsoever.

--
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/4/2018 8:42 PM, GS wrote:
....

Ambiguities arise when *numeric representation* displays as opposed to
*textual representation* displaying. Auric's code determines whether the
1st element represents d or m. Isn't this what the OP wants to figure out?
....

Yeah, but it depends on what point it is in the process; is it the
format of the cell that determines or the data string going into the
cell? I was presuming it was the latter in which case how the cell is
formatted is the answer to a question as to how Excel interpreted the
string but doesn't _necessarily_ mean that was what the date was from
whence it came.

--

On 5/4/2018 8:42 PM, GS wrote:
...

Ambiguities arise when *numeric representation* displays as opposed to
*textual representation* displaying. Auric's code determines whether the
1st element represents d or m. Isn't this what the OP wants to figure out?
...

Yeah, but it depends on what point it is in the process; is it the format of
the cell that determines or the data string going into the cell? I was
presuming it was the latter in which case how the cell is formatted is the
answer to a question as to how Excel interpreted the string but doesn't
_necessarily_ mean that was what the date was from whence it came.

That's not how it works! Dates aren't strings same as currencies arn't strings!

Excel uses the *system* date format by default to display the 'DateSerial' in
the cell to determine the dmy/mdy of a date based on that system's date format.
So.., when working with a file created in XP that has date cells, the
DateSerial is used to determine the datevalue in later OSs. This avoids all
ambiguity as to the true date being displayed.

The ambiguity for the user arises when the cell format is numeric (05/04/2018)
if/when the system date format isn't known. Using a textual format eliminates
the ambiguity because it displays the month name based on the DateSerial of the
value in the date cell. (What's important to remember here is that what cells
display isn't necessarily what they contain; -what we see is just how the cells
are/were formatted to display their contents.

--
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/5/2018 3:21 AM, GS wrote:
On 5/4/2018 8:42 PM, GS wrote:
...

Ambiguities arise when *numeric representation* displays as opposed
to *textual representation* displaying. Auric's code determines
whether the 1st element represents d or m. Isn't this what the OP
wants to figure out?
...

Yeah, but it depends on what point it is in the process; is it the
format of the cell that determines or the data string going into the
cell?* I was presuming it was the latter in which case how the cell is
formatted is the answer to a question as to how Excel interpreted the
string but doesn't _necessarily_ mean that was what the date was from
whence it came.

That's not how it works! Dates aren't strings same as currencies arn't
strings!
....

I'm fully aware of all that...wasn't what I was addressing which is the
date string as the OP wrote it in the query as the only piece of info
available, _NOT_ already stored but want to _be_ stored and wondering
which way is which to do so.

I said specifically the "the data string going _into_ the cell", once
it's _in_ the cell, yes, the cell is formatted as one or the other and
is unequivocal as far as Excel is concerned.

Doesn't _necessarily_ mean it was interpreted correctly when entered,
however, dependent upon the source.

We're talking past each other about different point in the process; ...

I've re-read the OPs ? and _guess_ I now think he probably was meaning
retrieved from a cell and therefore already in Excel so knowing which
way the cell is formatted answers the question.

--

No contest on the above...


I've re-read the OPs ? and _guess_ I now think he probably was meaning
retrieved from a cell and therefore already in Excel so knowing which way the
cell is formatted answers the question.

Exactly my point; - using a textual format eliminates the ambiguity!

Q: How did you get the underlined text?

--
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/5/2018 8:50 AM, GS wrote:
No contest on the above...


I've re-read the OPs ? and _guess_ I now think he probably was meaning
retrieved from a cell and therefore already in Excel so knowing which
way the cell is formatted answers the question.

Exactly my point; - using a textual format eliminates the ambiguity!

Well, yes, but OP didn't use one so that's sorta' a moot point...my take
when reading the question initially was that he had a date string from
somewhere unspecified and was wondering if any way to determine which it
might be...if he/she ever comes back and comments maybe we'll find out
what the real question was.

:)

Q: How did you get the underlined text?

Just use underscores; display result 'pends on the newsreader
recognizing them; T'Bird does for the most part; not all will/do.

--

On 5/5/2018 8:50 AM, GS wrote:
No contest on the above...


I've re-read the OPs ? and _guess_ I now think he probably was meaning
retrieved from a cell and therefore already in Excel so knowing which way
the cell is formatted answers the question.

Exactly my point; - using a textual format eliminates the ambiguity!

Well, yes, but OP didn't use one so that's sorta' a moot point...my take when
reading the question initially was that he had a date string from somewhere
unspecified and was wondering if any way to determine which it might be...if
he/she ever comes back and comments maybe we'll find out what the real
question was.

:)

Q: How did you get the underlined text?

Just use underscores; display result 'pends on the newsreader recognizing
them; T'Bird does for the most part; not all will/do.

I don't see how 2 different characters can occupy the same space and so was
expecting the 'flag' char as what we use to boldface, for example.

--
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/5/2018 9:33 AM, GS wrote:
On 5/5/2018 8:50 AM, GS wrote:
....

Q: How did you get the underlined text?

Just use underscores; display result 'pends on the newsreader
recognizing them; T'Bird does for the most part; not all will/do.

I don't see how 2 different characters can occupy the same space and so
was expecting the 'flag' char as what we use to boldface, for example.


There aren't two characters; it's unicode "combining characters"
character; is dependent upon font in use and the renderer to recognize
it; if it works it gives the effect; at worst one gets an idea of the
intended effect as just a leading/trailing underscore in plain text on
older newsreaders.

Not positive which release in T'bird it actually began to look like
underlined text; discovered it by pure accident as had used the form for
some emphasis on old groups with plain-vanilla readers "like since
forever" so is just a result of newer systems with an old and never
standard habit.

--

On 5/5/2018 8:50 AM, GS wrote:
No contest on the above...


I've re-read the OPs ? and _guess_ I now think he probably was meaning
retrieved from a cell and therefore already in Excel so knowing which
way the cell is formatted answers the question.

Exactly my point; - using a textual format eliminates the ambiguity!
....

My point was that if it is formatted as a date in Excel there's never
any ambiguity as to what Excel thinks it is -- the question for such as
OP gave is whether the format itself is the correct one for the given
input string -- and _THAT_ question is indeterminate from only the data
OP provided.

There's a _visual_ ambiguity to the user if they don't know whether the
originator of the spreadsheet (or they forget themselves) whether the
format is mm/dd or dd/mm and all there is contained in the particular
cell or group of cells are month/day combinations that are in range from
1:12 so there is no other unambiguous reference. That's the question
that any VBA or cell function can answer for those cells; it still can't
answer the question of whether those values when entered were indeed in
the one format or the other of the two choices before being input to the
cell with that formatting/interpretation.

Changing to mmm can remove that visual ambiguity; even that can't
resolve the underlying question on the actual value, it can reflect the
chosen coding of the underlying cell in a different display format.

--

On 5/5/2018 9:33 AM, GS wrote:
On 5/5/2018 8:50 AM, GS wrote:
...

Q: How did you get the underlined text?

Just use underscores; display result 'pends on the newsreader recognizing
them; T'Bird does for the most part; not all will/do.

I don't see how 2 different characters can occupy the same space and so was
expecting the 'flag' char as what we use to boldface, for example.


There aren't two characters; it's unicode "combining characters" character;
is dependent upon font in use and the renderer to recognize it; if it works
it gives the effect; at worst one gets an idea of the intended effect as just
a leading/trailing underscore in plain text on older newsreaders.

Not positive which release in T'bird it actually began to look like
underlined text; discovered it by pure accident as had used the form for some
emphasis on old groups with plain-vanilla readers "like since forever" so is
just a result of newer systems with an old and never standard habit.

That doesn't answer my Q! I'm guessing that I wrap the text to be underlined in
underscores same as I would asterisks to boldface; - so here goes:

_this is underlined text_

--
Garry

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

Ok, I'm not disputing any of what you state other than how Excel handles dates,
OR how date formats work. So here's an exercise you can do to see what I'm
saying:

Select A1:A3
While selected, key Ctrl+; followed by Ctrl+Enter
Today's date appears in numeric form 05/05/2018

Select A2 and set a Date format "14-Mar" in the Date NumberFormat list
Today's date appears in textual form 05-May

Select A3 and set its format to Text
Today's DateSerial appears 43225

Select A1:A2, type 43225 and key Ctrl+Enter
The display of A1:A2 doesn't change

Now we'll look at the system date format:
Select A1:A2
Since m/d are identical, type 5/6 then Ctrl+Enter and watch what happens. Now
type 6/5 then Ctrl+Enter into those cells. Now you see the system format for
dates displayed normally in A1 but without any ambiguity in A2.

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!

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

--

On 5/6/2018 7:46 AM, dpb wrote:
....

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


"...tried to enter as a string '5/5/2000' into two cells"

That was a typo, actually used '5/6/2000' so can see which is which from
=MONTH() or =DAY() of course.

--

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

I'm sorry it has taken me so long to get back, but I have been away from my computer ever since.

I will try some of you suggestions and see if any of them solves my challenge.

Thank you very much for your efforts.

Jan

And an explanation for my problem:

The original issue was and is, that I programmatically have to change alle dates to dd-mm-yyyy format. The dates are imported from other systems, and some of these are in the format mm-dd-yyyy. Dates that are already dd-mm-yyyy shall not be converted, so I want to check the format before conversion.

When the sheet is done, the data will exported to another system, who cannot check if the dates are in the right format, but expect them to be dd-mm-yyyy. Therefore I (or rather those who uses the sheet) have to maked sure all dates are formated correctly be the export.

Jan

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 3:52 AM, wrote:
And an explanation for my problem:

The original issue was and is, that I programmatically have to change alle dates to dd-mm-yyyy format. The dates are imported from other systems, and some of these are in the format mm-dd-yyyy. Dates that are already dd-mm-yyyy shall not be converted, so I want to check the format before conversion.

When the sheet is done, the data will exported to another system, who cannot check if the dates are in the right format, but expect them to be dd-mm-yyyy. Therefore I (or rather those who uses the sheet) have to maked sure all dates are formated correctly be the export.

If the data are correctly formatted based on the input from whence they
came, then there's no reason not to just convert all to dd-mm-yyyy; it's
a "do-nothing" for those that already are, so there's no penalty.

Auric's function will let you do it selectively, but there's probably
not enough of a time penalty that it makes any difference whether use it
or not.

Where you've still got a problem is if the data were imported but not
internally coded to match the external format; then you've still got the
issue that the values of ambiguous dates can't be distinguished solely
by their string value altho as GS points out, inside Excel they will
have been read according to the system default format in which case
switching the formatting changes the appearance in the cell but not the
value which raises the question of whether the dates in the sheet are
actually correct or not depending upon the input source and the system
setting and in what form the values were on input--was it from a date
string or a serial number?

--

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

"as GS points out, inside Excel they will have been read according to the
system default format"

See my latest reply to yours regarding "DateText" vs date interpretation of
text being imported with date values.

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