On 5/8/2018 9:45 AM, dpb wrote:
....

... How MS chose to interpret input dates
in Excel has to do with their penchant for thinking the world should
rotate around MS as much as anything else; that the system has no way
other than global change to the locale setting for importing dates from
alternate sources is symptomatic.

Back to the Excel-specific issue; my test that to me looks like it shows
that Excel is broke was to

1) Enter 5/6/2017 into A1 and A2 initially formatted as General
2) Observe the Excel converted both to Date as mm/dd/yyyy
3) Apply Custom format of dd/mm/yyyy to A2
4) Observe the display change to 6/5/2018 -- looks good, doesn't it?
5) Enter =A1 and =A2 into B1 and B2
6) Format B1 and B2 as d-mmm
7) Observe both read 6-May!!!

If one places focus on B2, one observes that it still contains 5/6/2018
and so all Excel has done is to change the output format; it doesn't
"believe" you really mean the data itself is in that format and fix it;
it's already past that stage and internally created the serial number
for the date as per the system locale setting.

That's why I have concern that the OP has a real problem in that what he
can display as _appearing_ correct for the two sets of data from the
foreign system very well may not actually be right at all.

Am I seeing this wrong, somehow? I'm certain that if one knows which
cell contains data from which source one can write code to fix it, but
afaict there's no way to get both into the spreadsheet correctly without
some such machination.

This also makes me wonder what happens to existing data if the locale is
change altho I've not tried to see...

It just looks to me like MS has used too big a club and made too many
assumptions regarding input format. Now again, yes, using a
non-ambiguous format for the external system would solve the issue; but
that's not what OP has...whether he can fix the system itself is
unknown; as bad as that situation is, I certainly have seen very many
which don't provide that flexibility to the end user.

--