CODEWITHSUNDEEP
excelvbadate
JimminyJim
JimminyJim

Reputation: 146

Excel auto interpretation of dates as string - switches between US and UK formats unpredictably

enter image description here Please see image for clearer description of the problem.

When pasting dates into excel it seems to SOMETIMES honor the local system settings for dates (set in Win10 intl.cpl) : in my case UK formatting i.e. DD-MM-YY, but othertimes it uses US formatting i.e. MM-DD-YY. I cannot workout the rules it is following, making it hard to accurately predict how it will interpret large pasted data sets of dates.

As a test, I took the string "02/11/2022 07:00" and pasted into excel. It correctly interpreted this in UK format as a date (in cell B2) as the date: 2-Nov-22 07:00

The source database where I'm copying the date datasets from appends various characters to some dates as markers that unfortunately cannot be removed before copying. So a date might look like: "02/11/2022 07:00*" (note the appended asterix). I need to remove the asterix in excel to make the date usable in excel.

That's where the wierdness comes in:

In the first case (row 3) if I use excel worksheet formulas to trim the asterix with LEFT function, the string remains a string and is not recognised as a date.

In the second case (row 4) if I use vba to trim the asterix with LEFT, the string is recognised in US format (i.e. not honoring local settings) and I get an incorrect date of 11-Feb-22 07:00. So two strange things here:

  1. the excel worksheet LEFT trim result does NOT get recognised as a date, the vba one DOES; and
  2. the vba trim result IS recognised as a date but is interpreted with US settings, even though the local settings are honored in the original 'clean' string case

In the third case (row 5) if I use vba to trim and CDate the string in one line VBA line, excel recognises a date and recognises it correctly in UK format. However if this is done in two steps (trim in one line, paste to cell, and then Cdate the resultant string), excel interferes when the cell gets hold of the string and I get a US date recognised in the middle.

What is most confusing is that if i post a 'clean' date string, excel honors the local interpretation. If I need to manipulate the string to remove artefacts and put a cleaned string in a cell, it doesn't honor local interpretation.

Note: I understand the difference of regional cell FORMATTING which just tells excel how to DISPLAY the date. This question is to do with excel converting a string to a numeric value by sometimes assuming a US date, and othertimes assuming a UK date!

Any definitive advice on how to force excel/vba into using the local date format when it INTERPRETS date strings?

Upvotes: 1

Views: 146

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Assuming your pasted strings do not include the visible quotation marks you show

  • Row 1: Normal behavior
  • Row 2: The string functions (eg LEFT) return text which will not be auto-converted. To convert to a date value, wrap the results with either the double-unary (--), VALUE function, or DATEVALUE function.
  • Row 3: VBA is US-centric in converting unqualified date strings
  • Row 4: CDate respects the windows regional settings.

Upvotes: 2

Related Questions