Avatar billede omn Mester
31. januar 2021 - 17:04 Der er 6 kommentarer og
2 løsninger

Date standard

From EXCEL I import a number of dates in format mm/dd/yyyy and they shows up in Access tabel as short text field. I am trying to convert to Danish date standard by copying field to another field with Date/Time as format.  If date = 1/13/2021 it ends up as 13-01-2021 and it is OK. But if I receive a date = 1/12/2021 it ends up as 01-12-2021 and it is not Danish short format. It seems to go wrong for all dates where it you can switch MM and DD and still have a valid date in format dd-mm-yyyyt.
How can I handle this situation.
Best regards
OMN.
Avatar billede Gustav Ekspert
31. januar 2021 - 17:55 #1
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
Avatar billede bvirk Guru
31. januar 2021 - 22:02 #2
Vælg MDÅ og datoseperator '/' for feltet i import wizardens avanceret indstilling.
Avatar billede terry Ekspert
01. februar 2021 - 11:28 #3
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.
Avatar billede omn Mester
01. februar 2021 - 11:42 #4
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.
Avatar billede Gustav Ekspert
01. februar 2021 - 12:00 #5
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
Avatar billede terry Ekspert
01. februar 2021 - 12:49 #6
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))
Avatar billede terry Ekspert
01. februar 2021 - 12:50 #7
dt = DateSerial(result(2), result(0), result(1))
Avatar billede omn Mester
01. februar 2021 - 13:09 #8
Thanks for this feed-back. Both of them works very well.
OMN
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Dyk ned i databasernes verden på et af vores praksisnære Access-kurser

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester