Date standard

Feed your US formatted text dates to this function, and it will return true date values, which you can format as you like for display:

' Converts a US formatted date/time string to a date value.
'
' Examples:
'  7/6/2016 7:00 PM    -> 2016-07-06 19:00:00
'  7/6 7:00 PM        -> 2018-07-06 19:00:00  ' Current year is 2018.
'  7/6/46 7:00 PM      -> 1946-07-06 19:00:00
'  8/9-1982 9:33      -> 1982-08-09 09:33:00
'  2/29 14:21:56      -> 2039-02-01 14:21:56  ' Month/year.
'  2/39 14:21:56      -> 1939-02-01 14:21:56  ' Month/year.
'  7/6/46 7            -> 1946-07-06 00:00:00  ' Cannot read time.
'  7:32                -> 1899-12-30 07:32:00  ' Time value only.
'  7:32 PM            -> 1899-12-30 19:32:00  ' Time value only.
'  7.32 PM            -> 1899-12-30 19:32:00  ' Time value only.
'  14:21:56            -> 1899-12-30 14:21:56  ' Time value only.
'
' 2018-03-31. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function CDateUs( _
    ByVal Expression As String) _
    As Date
   
    Const PartSeparator As String = " "
    Const DateSeparator As String = "/"
    Const DashSeparator As String = "-"
    Const MaxPartCount  As Integer = 2

    Dim Parts          As Variant
    Dim DateParts      As Variant
   
    Dim DatePart        As Date
    Dim TimePart        As Date
    Dim Result          As Date
   
    ' Split expression into maximum two parts.
    Parts = Split(Expression, PartSeparator, MaxPartCount)
   
   
    If IsDate(Parts(0)) Then
        ' A date or time part is found.
        ' Replace dashes with slashes.
        Parts(0) = Replace(Parts(0), DashSeparator, DateSeparator)
        If InStr(1, Parts(0), DateSeparator) > 1 Then
            ' A date part is found.
            DateParts = Split(Parts(0), DateSeparator)
            If UBound(DateParts) = 2 Then
                ' The date includes year.
                DatePart = DateSerial(DateParts(2), DateParts(0), DateParts(1))
            Else
                If IsDate(CStr(Year(Date)) & DateSeparator & Join(DateParts, DateSeparator)) Then
                    ' Use current year.
                    DatePart = DateSerial(Year(Date), DateParts(0), DateParts(1))
                Else
                    ' Expression contains month/year.
                    DatePart = CDate(Join(DateParts, DateSeparator))
                End If
            End If
            If UBound(Parts) = 1 Then
                If IsDate(Parts(1)) Then
                    ' A time part is found.
                    TimePart = CDate(Parts(1))
                End If
            End If
        Else
            ' A time part it must be.
            ' Concatenate an AM/PM part if present.
            TimePart = CDate(Join(Parts, PartSeparator))
        End If
    End If
   
    Result = DatePart + TimePart
       
    CDateUs = Result

End Function

Vælg MDÅ og datoseperator '/' for feltet i import wizardens avanceret indstilling.

Normally the advanced settings isnt available with Excel sheets, but if you rename to csv then it is.

Omn: Normally date fields are controlled by the PC's regional settings. So I'm puzzled as to how the dates in the Excel sheet are in US format, but when you import the result, although wrong, is in Danish.

Maybe some explanation is needed.

Thanks for comments. The situation is that I have dates in text format from EXCEL (not in US date format) but it looks like.
I have tested solution from Gustav in this setup.

Dim txt As Date
Dim txt1 As String
'**  This works
txt = #1/12/2021#
Call CDateUs(txt)
MsgBox txt      -> 12-01-2021

'**  but this does not work - and this is my situation. I have received all dates in thisText format - 10 character.
txt1 = "1/12/2021"
Call CDateUs(txt1)
MsgBox txt1 - > 01/12/2021

@ Gustav - do you have any further comments. What am I doing wrong? What can I do to get your rutine to work?
Thanks in advance.
OMN.

You will have to use the output of the function:

Dim TrueDate As Date

txt1 = "1/12/2021"
TrueDate = CDateUs(txt1)
MsgBox Format(TrueDate, "yyyy-mm-dd")
' - > 2021-01-12

Dim txt1 As String
Dim dt As Date
Dim result() As String


    txt1 = "1/12/2021"
    result() = Split(txt1, "/")

    MsgBox DateSerial(result(2), result(0), result(1))

dt = DateSerial(result(2), result(0), result(1))

Thanks for this feed-back. Both of them works very well.
OMN