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