Date Format Issue in the Data Received

[anish.ms]

Hi Experts,

I have a situation where the dates in the data received is in "mm/dd/yyyy" and my system format is "dd/mm/yyyy". Hence, my system has converted all the dates where the middle part is less than 13 as date considering date as month and the other dates remain unconverted.

For example
04-Mar-2021 11:55:21 - Actual date is 03-April-2021
5/26/2021 12:54:51 PM - Remains unconverted

I have a lengthy formula in the WB to correct this issue. Also, I was trying to create a UDF for easy reference in future. I have started with the below code but its not working completely, requesting your help here-



Public Function FormatDate(ByRef X) As Date
Dim xTmp
If IsDate(X) Then
FormatDate = DateSerial(Year(X), Day(X), Month(X))
Else
xTmp = Split(X, " ")
FormatDate = xTmp(0)
End If
End Function


Thanks in advance!

[Paul_Hossler]

Try something like this

If it's a date according to your regional locale settings, it just returns it IAW your RLS

If it's a mm dd yyyy (US setting) string, then it splits it and returns a real date IAW your RLS

You can format it using the Format Number on the worksheet

29109



Option Explicit


Public Function MyFormatDate(X As Variant) As Date
Dim xTmp

'dd/mm/yyyy
If IsDate(X) Then
MyFormatDate = DateSerial(Year(X), Month(X), Day(X))

'mm-dd-yyyy
ElseIf InStr(X, "-") > 0 Then
xTmp = Split(X, "-")
MyFormatDate = DateSerial(xTmp(2), xTmp(0), xTmp(1))

'mm/dd/yyyy
ElseIf InStr(X, "/") > 0 Then
xTmp = Split(X, "/")
MyFormatDate = DateSerial(xTmp(2), xTmp(0), xTmp(1))
End If
End Function

[anish.ms]

Thanks Paul for your help always!
Actually, if it's a date according to my regional settings, I wanted it in reverse order.


If IsDate(X) Then
MyFormatDate = DateSerial(Year(X), Day(X), Month(X))

But, if I do the above changes, the dates are not coming properly for the non date formats (Elseif)
29111
I think the second case is also considered as date and it is not going to Elseif condition

[p45cal]

How is the data received into your sheet? By pasting?
Excel, while trying to be helpful (by converting text to dates proper on pasting) ends up being counterproductive (some dates may not be interpreted as dates at all, and most would be incorrectly interpreted).
One way to get around this is to format your receiving cells as Text before you paste, which is not that easy as you have to work out how many cells you need to change the format of (formatting a whole column will tend to make the sheet a bit resource-heavy). Then you can do a Text-to-columns in situ specifying the format of the source strings. The dates are then calculated to proper Excel dates and can be shown any way you wish.
A macro can do this. The aim is to run the macro straight after you've pasted something, while the area you've pasted to is still selected. It converts the selected area to Text format then re-pastes the data which has just been pasted, then it does a Text-to-columns in situ which converts the strings to proper (and correct) dates.

Two versions of the macro (there's only one character difference between the two)
To bring in dates which are US type:

Sub BringIn_mdy_Dates()
Set rngDestn = Selection
rngDestn.NumberFormat = "@"
ActiveSheet.PasteSpecial Format:="Text"
rngDestn.TextToColumns FieldInfo:=Array(1, 3) 'mm/dd/yy source strings
End Sub

To bring in dates which are UK/European type:

Sub BringIn_dmy_Dates()
Set rngDestn = Selection
rngDestn.NumberFormat = "@"
ActiveSheet.PasteSpecial Format:="Text"
rngDestn.TextToColumns FieldInfo:=Array(1, 4) 'dd/mm/yy source strings
End Sub

The idea would be to assign a keyboard shortcut to the macro you're going to use, then use that shortcut immediately after you've pasted.
This will only work on a single column of data.
As it stands, if there are times as well as dates in the imported data I don't think it will work well and will need a re-write.

[anish.ms]

Hi p45cal,
Thanks for your time and help!
The data is a system generated report shared by a user.
I tried the codes and I'm getting error in following line
ActiveSheet.PasteSpecial Format:="Text"

[p45cal]

The data is a system generated report shared by a user.
Tell me more about this and how the data arrives in the sheet.

[anish.ms]

Hi p45cal,

I have attached the data received as it is. I have deleted some sensitive information and converted the file to binary to reduce the size and upload here.
It's a report generated from our internal software and I understand from the report heading the date format is dd/mm/yyyy and my system RLS is English (India) with short date format of dd-mm-yyyy
This is how the date is showing for me and I get data like this quite often
29112
If I format the cells to date, it takes date as month and wherever the date is greater than 12, it doesn't get formatted as date.
Hope, I was able to explain the situation.
Thanks,

[arnelgp]

Public Function FormatDate(ByVal X) As Date
Dim xTmp, sFormat As String
Dim delim As String
X = Split(X, " ")(0) & ""
delim = LocalDateDiv()
If IsValidDate(X, sFormat) = False Then
xTmp = Split(X, delim)
Select Case Left$(sFormat, 1)
Case "m"
xTmp = CDate(xTmp(0) & "/" & xTmp(1) & "/" & xTmp(2))
Case "y"
xTmp = CDate(xTmp(1) & "/" & xTmp(2) & "/" & xTmp(0))
End Select
FormatDate = xTmp
Else
FormatDate = CDate(X)
End If
End Function



Option Explicit


'© 2003 Roger Gilchrist <rojagilkrist@hotmail.com>
'based on the VBhelper upload 'howto_validate_date.zip'
'This code internationalises it to work with
'whatever your local date format and date divisor are.
'Also includes a way of coping with
'ASCII/UniCode data (pre-Win2K or post-Win2K)
'
'Unless you have access to machines using different date systems
'or feel like fiddling with your machine's regional settings
'you'll have to take this on faith ;)
'
Private Const LOCALE_SDATE As Long = &H1D ' get the date divisor
Private Const LOCALE_ILDATE As Long = &H22 ' get the long DMY order
Private Const LOCALE_IDATE As Long = &H21 ' get the DMY order
Private Const LOCALE_SSHORTDATE As Long = &H1F ' get the short date
Private Const LOCALE_SLONGDATE As Long = &H20 ' get the long date format
'You can find many other localisation constants
'in VB help under 'Locale Information ' No values but lots of explanations
'or in API viewer search for 'LOCALE_' ' No explanations but has values
#If VBA7 Then
Private Declare PtrSafe Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, _
ByVal LCType As Long, _
ByVal lpLCData As String, _
ByVal cchData As Long) As Long
#Else
Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, _
ByVal LCType As Long, _
ByVal lpLCData As String, _
ByVal cchData As Long) As Long
#End If


Public Function InputDateFormat() As String


'this creates the date format to check with Format in IsValidDate


Select Case LocalDMY
Case 0
InputDateFormat = "m" & LocalDateDiv & "d" & LocalDateDiv & "yy"
Case 1
InputDateFormat = "d" & LocalDateDiv & "m" & LocalDateDiv & "yy"
Case 2
InputDateFormat = "yy" & LocalDateDiv & "m" & LocalDateDiv & "d"
End Select


End Function


Public Function IsValidDate(ByVal txt As String, _
Optional strFormat As String) As Boolean


'internationalized version
'Optionally returns the format that it recognised
'otherwise for days whose number is < 12 you may not be sure
'if 2/12/99 is 2nd December or 12th Febuary if you don't
'know which system your machine is using


strFormat = InputDateFormat
On Error GoTo DoneChecking
IsValidDate = (Format$(CDate(txt), strFormat) = txt)


Exit Function


DoneChecking:
strFormat = "INVALID DATE: " & txt & " Check format = " & strFormat


End Function


Public Function LocalDateDiv() As String


' gets the local date divisor


LocalDateDiv = LocalizationData(LOCALE_SDATE)


End Function


Public Function LocalDMY() As Integer


'gets the D M Y order
'Returns 0,1, or 2
'0 Month-Day-Year
'1 Day-Month-Year
'2 Year-Month-Day


LocalDMY = LocalizationData(LOCALE_ILDATE)
'LocalDMY = LocalizationData(LOCALE_IDATE) would also work
'(I can't imagine anyone uses different ones in a single local)


End Function


Public Function LocalizationData(ByVal LData As Long) As String


'This is a general routine to read whatever bit of data
'you want based on the constants fed to it as LData

Dim stBuff As String * 255
Dim ret As Long


ret = GetLocaleInfo(1024, LData, ByVal stBuff, Len(stBuff))
If ret Then
LocalizationData = Left$(stBuff, ret - IIf(StrConv(stBuff, vbFromUnicode) = stBuff, 0, 1))
'NOTE the IIf bit of code determines whether you are dealing with an ASCII or UniCode system and
'trims the string accordingly
End If


End Function


Public Function LocalLongDate() As String


LocalLongDate = LocalizationData(LOCALE_SLONGDATE)


End Function


Public Function LocalShortDate() As String


LocalShortDate = LocalizationData(LOCALE_SSHORTDATE)


End Function

[p45cal]

I can glean a lot from your file and picture and can remedy it quite easily, but I'd like to know how you receive the report, that is before you even think of opening it in any application (let alone Excel).
What is the extension of the file (the bit after the last dot in the file name)?

[anish.ms]

Hi arnelgp (http://www.vbaexpress.com/forum/member.php?74556-arnelgp)

it is not working due to some reason. I'm getting value error for the UDF FormatDate

[anish.ms]

Hi p45cal,
I received the file in .xlsx format

[p45cal]

I received the file in .xlsx format
That's the problem!
The file's been created elsewhere, not on a machine with a DMY locale but an MDY locale.
When the data has landed in Excel the strings like:
31/05/2021 are not able to be translated to proper dates because in a MDY locale the 31st month is impossible, so the string is not interpreted but left as it was. (Row 5 of your sheet)
Later in row 30700 you have a number 44535 which is actually a date (format the cell as a date with month showing as text rather than a number) 5 December 2021. This has been the string 12/05/2021 interpreted as the day 5 of the 12th month, of 2021, which is a MDY system. This date is wrong! It should be 12 May 2021 (just look at the name of the sheet on the tab: April - June 21).
This is the fault of the people who have prepared the .xlsx file that you receive. Ideally, you should point out their mistake and get them to send you valid .xlsx files. Do you have access to the report before it gets into Excel? It would be safer for you to do your own import into Excel.

Now to solve your problem of the duff dates you're receiving and putting them right.
All the dates which are purportedly proper Excel dates are WRONG (these are the numbers you have in column C)
All the un-interpreted dates are strings which need to be converted to the correct dates regardless of your locale (we know they're in DMY format).
This macro should do that.
It works on the single column of cells that are selected before you run it.
It puts the results in the single column 4 columns to the right of the selected cells (you should format these result cells as dates). I've done this so that you can check the dates are correct because it leaves the selected cells untouched.
The 'single column 4 columns to the right of the selected cells' is determined by the last line:
Selection.Offset(, 4).Value = myVals
You can change that 4 for a different value if you want.
Ultimately, it's been designed to replace the values in the selected cells which you do by deleting the .Offset line altogether and enabling the
'Selection.Value = myVals
by removing the leading apostrophe.

Here's the code:
Sub blah()
myVals = Selection.Value
On Error Resume Next
For i = 1 To UBound(myVals)
If IsNumeric(myVals(i, 1)) Then
y = CDate(myVals(i, 1))
myVals(i, 1) = DateSerial(Year(y), Day(y), Month(y))
Else
y = Split(myVals(i, 1), "/")
myVals(i, 1) = DateSerial(y(2), y(1), y(0))
End If
Next i
Selection.Offset(, 4).Value = myVals
'Selection.Value = myVals
End Sub
Put it in a standard code-module rather than a sheet's code module.

[anish.ms]

Thanks p45cal!
Exactly the issue is date format change between the systems - where it is downloaded and where it is used
I don't have access to this report.
I tried your code and its working fine.
I have a doubt here - If I change the column (C) format to date and apply this macro, why 31/05/2021 (string) is converted as 05-Jul-2023 ?

And I have changed the code to add a new column after the selection to save the corrected dates
ActiveCell.EntireColumn.Offset(0, 1).Insert
Selection.Offset(, 1).Value = myVals

I have added the code to my personal.xlsb as get such files quite often. And I was testing on a different file where there is time along with the date. The results are correct however, for string cases it is taking time as well and for numeric cases only dates are there.

44259.49677 03-04-2021
5/26/2021 12:54:51 PM 26-05-2021 12:54

I tried adding time serial but its not giving the correct result
myVals(I, 1) = DateSerial(Year(y), Day(y), Month(y)) & " " & TimeSerial(Hour(y), Minute(y), Second(y))

[p45cal]

Yes you're quite right, change that first line from
myVals = Selection.Value
to:
myVals = Selection.Value2

(5 July 2023 is 31 months and 5 days after the beginning of 2021)

[arnelgp]

see the modified function.
https://www.dropbox.com/scl/fi/8llru4uy716js46kjwg8p/Prescription-Report-Apr-June-21-1.xlsb?dl=0&rlkey=1zq85b8knlhqlq2du5izlejsg

[p45cal]

And I was testing on a different file where there is time along with the date. The results are correct however, for string cases it is taking time as well and for numeric cases only dates are there.

44259.49677 03-04-2021
5/26/2021 12:54:51 PM 26-05-2021 12:54
I hadn't immediately noticed you updated your previous reply.
Do yo want time to be included in the results, or just dates?

[p45cal]

And I was testing on a different file where there is time along with the date. The results are correct however, for string cases it is taking time as well and for numeric cases only dates are there.

44259.49677 03-04-2021
5/26/2021 12:54:51 PM 26-05-2021 12:54
You shouldn't be getting strings like 5/26/2021 12:54:51 PM because these would have been converted to Excel dates (numbers) on the MDY system. Make sure these string really are strings by temporarily changing/chacking thos cells are formatted General. If you are getting strings like this then this is an entirely new problem.

If you're still looking to get the time element from dates/times strings or numbers, please supply a workbook with these in it.

This shows the importance of what I said earlier:
'This is the fault of the people who have prepared the .xlsx file that you receive. Ideally, you should point out their mistake and get them to send you valid .xlsx files.'
Why should the onus be on you to correctly handle incorrect reports sent by other people? Get them to send you correct reports, or send you the reports before they've been put into Excel.

[Paul_Hossler]

Been offline for awhile visiting the grandkids

So I'm a little confused by ...

Post #1


I have a situation where the dates in the data received is in "mm/dd/yyyy" and my system format is "dd/mm/yyyy".

Post #3


Actually, if it's a date according to my regional settings, I wanted it in reverse order.


I appears from your attached file that the 'dates' can be strings (25/03/2021) or longs (49321) or dates (1/1/2021) depending on the RLS of the source and/or your computer

1. For flexibility, I like to separate 'data' from the 'presentation' of the data

2. For defensive programming I think it'd be good to use the UDF to always convert the inputs (Col B) to a standard Excel 'Long' (Col E)

From here, just format the standard Long to whatever format you want (Col F and G)


29119




Option Explicit


Public Function MyFormatDate(X As Variant) As Variant
Dim xTmp As Variant

On Error GoTo ErrorHappened

If InStr(X, " ") > 0 Then
X = Left(X, InStr(X, " ") - 1)
End If


'44556
If IsNumeric(X) Then
MyFormatDate = DateSerial(Year(X), Month(X), Day(X))
Exit Function
End If

'm/d/yyyy
If IsDate(X) Then
MyFormatDate = DateSerial(Year(X), Month(X), Day(X))
Exit Function
End If

If Len(X) <> 10 Then Err.Raise xlErrNum

'xx-xx-yyyy
If InStr(X, "-") > 0 Then
xTmp = Split(X, "-")
MyFormatDate = DateSerial(xTmp(2), xTmp(0), xTmp(1))
Exit Function
End If

'xx/xx/yyyy
If InStr(X, "/") > 0 Then
xTmp = Split(X, "/")
MyFormatDate = DateSerial(xTmp(2), xTmp(0), xTmp(1))
Exit Function
End If


Exit Function

ErrorHappened:
MyFormatDate = CVErr(xlErrNum)
End Function





I just used a sample of your file

[anish.ms]

You shouldn't be getting strings like 5/26/2021 12:54:51 PM because these would have been converted to Excel dates (numbers) on the MDY system. Make sure these string really are strings by temporarily changing/chacking thos cells are formatted General. If you are getting strings like this then this is an entirely new problem.

If you're still looking to get the time element from dates/times strings or numbers, please supply a workbook with these in it.

This shows the importance of what I said earlier:
'This is the fault of the people who have prepared the .xlsx file that you receive. Ideally, you should point out their mistake and get them to send you valid .xlsx files.'
Why should the onus be on you to correctly handle incorrect reports sent by other people? Get them to send you correct reports, or send you the reports before they've been put into Excel.

Thanks p45cal for your time
Being in audit, I get lot of files from the process owners. I will ask them to change the RLS in case of such issues.
I don't want time from the dates. Please find attached a sample file with date and time for your reference. I think if time is required for me for some working, I can take it in a new column using text to column or right function.
Thanks

[anish.ms]

Been offline for awhile visiting the grandkids

So I'm a little confused by ...



Hi Paul,
Hope you had a great time with grandkids
Sorry for the confusion.
All the dates in the sample are within April and May.

29121


The problem with the file is (copied from the response of p45cal, as he has mentioned it very clearly than mine)
The file's been created elsewhere, not on a machine with a DMY locale but an MDY locale.
When the data has landed in Excel the strings like:
31/05/2021 are not able to be translated to proper dates because in a MDY locale the 31st month is impossible, so the string is not interpreted but left as it was. (Row 5 of your sheet)
Later in row 30700 you have a number 44535 which is actually a date (format the cell as a date with month showing as text rather than a number) 5 December 2021. This has been the string 12/05/2021 interpreted as the day 5 of the 12th month, of 2021, which is a MDY system. This date is wrong! It should be 12 May 2021 (just look at the name of the sheet on the tab: April - June 21).

Thanks,

[p45cal]

I don't want time from the dates. Please find attached a sample file with date and time for your reference.
So this is a different problem; it's the other way round: The report has produced strings which are in MDY format, and has been brought into a version of Excel with a DMY locale.
So again, the numeric dates are wrong and the uninterpreted strings need to be interpreted.
This macro should do that. Times are ignored.
By the way, Text-to-columns performs badly for dates with times.

Sub blah2()
myVals = Selection.Value2
On Error Resume Next
For i = 1 To UBound(myVals)
If IsNumeric(myVals(i, 1)) Then
y = CDate(myVals(i, 1))
myVals(i, 1) = DateSerial(Year(y), Day(y), Month(y))
Else
y = Split(Split(Application.Trim(myVals(i, 1)))(0), "/")
myVals(i, 1) = DateSerial(y(2), y(0), y(1))
End If
Next i
Selection.Offset(, 4).Value = myVals
'Selection.Value = myVals
End Sub


It looks like you're getting your reports from a variety of different regions. Get them to send you correct reports in Excel or get them to send the output from their systems before they've been brought into Excel. Then you'll have control of the import into Excel. Consider using Power Query to bring those reports into Excel.

[Paul_Hossler]

Later in row 30700 you have a number 44535 which is actually a date (format the cell as a date with month showing as text rather than a number) 5 December 2021. This has been the string 12/05/2021 interpreted as the day 5 of the 12th month, of 2021, which is a MDY system. This date is wrong! It should be 12 May 2021 (just look at the name of the sheet on the tab: April - June 21).

As P45cal has said, the 100% proper way is to have the file generator correct the report xlsx

However, in my experience in business, that is typically 'not gunna happn'

SO I'd still vote for defensive programming to protect myself from others and just fix their 'mistakes'

If it's ALWAYS from a DD/MM/YYYY system your #1 seems pretty close, FixDates() below. FixDates2() has a parameter 'DMY' to allow options

If the source is a mixture of DD/MM/YYYY and MM/DD/YYYY then it's a lot more complicated since is "12/5/2021' = 'May 12, 2021' or = 'Dec 5, 2021'???:devil2:

29123


Option Explicit


'ALWAYS from a DD/MM/YYYY system
Public Function FixDates(X As Variant) As Variant
Dim xTmp As Variant

On Error GoTo ErrorHappened

If InStr(X, " ") > 0 Then
X = Left(X, InStr(X, " ") - 1)
End If

'44556
If IsNumeric(X) Then
FixDates = DateSerial(Year(X), Day(X), Month(X))
Exit Function
End If

If Len(X) <> 10 Then Err.Raise xlErrNum

'xx/xx/yyyy
If InStr(X, "/") > 0 Then
xTmp = Split(X, "/")
FixDates = DateSerial(xTmp(2), xTmp(1), xTmp(0))
Exit Function
End If


Exit Function

ErrorHappened:
FixDates = CVErr(xlErrNum)
End Function




'SOMETIMES from a DD/MM/YYYY system
Public Function FixDates2(X As Variant, Optional SourceFormat As String = "DMY") As Variant
Dim xTmp As Variant

On Error GoTo ErrorHappened

If InStr(X, " ") > 0 Then
X = Left(X, InStr(X, " ") - 1)
End If

If SourceFormat = "DMY" Then
'44556
If IsNumeric(X) Then
FixDates2 = DateSerial(Year(X), Day(X), Month(X))
Exit Function
End If

If Len(X) <> 10 Then Err.Raise xlErrNum

'xx/xx/yyyy
If InStr(X, "/") > 0 Then
xTmp = Split(X, "/")
FixDates2 = DateSerial(xTmp(2), xTmp(1), xTmp(0))
Exit Function
End If


Else
'44556
If IsNumeric(X) Then
FixDates2 = DateSerial(Year(X), Month(X), Day(X))
Exit Function
End If

'm/d/yyyy
If IsDate(X) Then
FixDates2 = DateSerial(Year(X), Month(X), Day(X))
Exit Function
End If

If Len(X) <> 10 Then Err.Raise xlErrNum

'xx/xx/yyyy
If InStr(X, "/") > 0 Then
xTmp = Split(X, "/")
FixDates2 = DateSerial(xTmp(2), xTmp(0), xTmp(1))
Exit Function
End If
End If


Exit Function

ErrorHappened:
FixDates2 = CVErr(xlErrNum)
End Function



Add: I see fromBook1.xlsx that sometimes you get MM/DD/YYYY formatted data

29126


So the BIG question might be: "Within a file, is ALL the data consistently formatted?" such that you could choose a different macro or function to use

[anish.ms]

Thanks p45cal for your help and advice :bow:
Just to understand, if the situation is like - report is exported to excel from a software with DMY format in a pc with MDY locale. And the user forward it to me without opening the excel file, will this issue happen subject to my pc is DMY locale?

[anish.ms]

Thanks Paul for your help, advice and giving the option of UDF :bow:

So the BIG question might be: "Within a file, is ALL the data consistently formatted?" such that you could choose a different macro or function to use
Yes, within a file, all the data is consistently formatted

[p45cal]

if the situation is like - report is exported to excel from a software with DMY format in a pc with MDY locale. And the user forward it to me without opening the excel file, will this issue happen subject to my pc is DMYI can't answer that! If the application exports directly to Excel I don't know how it does it. You must experiment. What is the software which does this? Is it a well-known application or suite?

[anish.ms]

What is the software which does this? Is it a well-known application or suite?
Its not a well known application. Its a Hospital Information System developed for us by a software company in India.
No worries, I will experiment it.
Thanks

[Paul_Hossler]

My opinions:


Regional issues combined with Formatting is very trick to sort out, many variables

The very bottom most layer of dates/times is a Double, which can be 1) formatted on the worksheet by the user or 2) by Excel 'trying to be helpful'

So a VALUE = 44555.25 can be formatted as
12/25/2021
Saturday, December 25, 2021
6:00:00 AM

in the US (MM/DD/YYYY)

or as

25/12/2021
25 December 2021
06:00:00

in the UK (DD/MM/YYYY)

Underneath the formatting it is still a Double


Date/times entered via VBA or with a formula on the worksheet using DateSerial (...) or DATE(...) will adjust for regional settings across different PCs.

Similarly if the worksheet formatting uses one of the 'region-aware' formatting options, the values will adjust

29135
29134

In the above, I created a simple demo worksheet and just changed my locale. The formatting automatically adjusted to the new locale



The difficulty is when a date is default formatted as dd/mm/yyyy using PC#1's RLS but imported / interpreted by PC#2 with RLS mm/dd/yyyy

So that's why 12/5/2021 is ambiguous: is it Dec 5 or is it May 12?

My approach for things like this is

1. NEVER trust the user

2. ALWAYS clean the data back to something 'standard'

3. FORMAT using one of the region-aware options

4. Don't FIGHT Excel, but try to work with it


So you could have a Sub that reads the 'date' column, determines (OK, assumes) the region formats, then converts to Excel standard serial date doubles, and then formats the column a Short Date if that's what you want

[anish.ms]

Thanks Paul for your time in explaining it in this detail. It's is very insightful :thumb