How to convert UPS Date to Date Format

[MattC66]

Hi I have data from UPS Worldship software imported in to Access. The Date Shipped comes over as numbers

SHIPDATE

20210414101853

20210414110304

20210414111414

20210414111941

Its Year, Month, Day and I assume time. I'd like to setup a query to display the date and time. What would be the best method?

 

[Micron]

What's 111414, 11:14:14 AM? Probably will need a function due to all the mashed parts.

 

[jackd]

Note: my regional setting has Date format DD-MMM_YY.
So final output reflects my regional setting.


' ----------------------------------------------------------------
' Procedure Name: Convert2Date
' Purpose: Convert a 14 digit UPSWorldship string to Date
' Procedure Kind: Function
' Procedure Access: Public
' Parameter strUPSWorldship (String): Incoming string from UPSWorldship
' Return Type: Date
' Author: Jack
' Date: 16-Apr-21
' ----------------------------------------------------------------
Function Convert2Date(strUPSWorldship As String) As Date

10    Dim strUPS As String: strUPS = strUPSWorldship
      Dim sYear As String
      Dim sMonth As String
      Dim sDay As String
      Dim sHour As String
      Dim sMin As String
      Dim sSec As String
20    sYear = Left(strUPS, 4)
30    sMonth = Mid(strUPS, 5, 2)
40    sDay = Mid(strUPS, 7, 2)
50    sHour = Mid(strUPS, 9, 2)
60    sMin = Mid(strUPS, 11, 2)
70    sSec = Mid(strUPS, 13, 2)
80    Convert2Date = CDate(sYear & "/" & sMonth & "/" & sDay & " " & sHour & ":" & sMin & ":" & sSec)
End Function

Test routine1:

Sub testUpsdateTime()
      Dim s As String
10    s = "20210414211414"
30    Debug.Print s, Convert2Date(s)
End Sub

20210414211414 14-Apr-21 9:14:14 PM

Good luck with your project.

Test routine2:

Sub testUpsdateTime()
      Dim s As String
10    s = "20210414113752"
30    Debug.Print s, Convert2Date(s)
End Sub

20210414113752 14-Apr-21 11:37:52 AM

 

[Micron]

Wow, that's almost exactly what I wrote while I was waiting for an answer. I guess I shouldn't have sat on it while waiting to find out if it was AM or PM, or even time for that matter. Oh well...

 

[MattC66]

What's 111414, 11:14:14 AM? Probably will need a function due to all the mashed parts.

I assume it is time. I don't need that part. Just Year, Month and Day works for me.

 

[Micron]

Then comment out the part of the code that deals with the time I guess. If you ever change your mind it will still be there.

 

[jackd]

Here's an adjustment:

' ----------------------------------------------------------------
' Procedure Name: Convert2Date
' Purpose: Convert a 14 digit UPSWorldship string to Date with option to include Time
' Procedure Kind: Function
' Procedure Access: Public
' Parameter strUPSWorldship (String):
' Parameter withTime (Boolean): False: Date only --True: Date and Time
' Default to Date Only
' Return Type: Date
' Author: Jack
' Date: 16-Apr-21
' ----------------------------------------------------------------
Function Convert2Date(strUPSWorldship As String, Optional withTime As Boolean = False) As Date
       
10    Dim strUPS As String: strUPS = strUPSWorldship
      Dim sYear As String
      Dim sMonth As String
      Dim sDay As String
      Dim sHour As String
      Dim sMin As String
      Dim sSec As String
      Dim sHold As String
20    sYear = Left(strUPS, 4)
30    sMonth = Mid(strUPS, 5, 2)
40    sDay = Mid(strUPS, 7, 2)
50    sHour = Mid(strUPS, 9, 2)
60    sMin = Mid(strUPS, 11, 2)
70    sSec = Mid(strUPS, 13, 2)
80    sHold = sYear & "/" & sMonth & "/" & sDay & " " & sHour & ":" & sMin & ":" & sSec
90    If withTime Then
100   Convert2Date = CDate(sYear & "/" & sMonth & "/" & sDay & " " & sHour & ":" & sMin & ":" & sSec)
110   Else
120   Convert2Date = CDate(sYear & "/" & sMonth & "/" & sDay)
130   End If
End Function

Sub testUpsdateTime()
      Dim s As String
10    s = "20210414113752"
20    Debug.Print s, Convert2Date(s)
End Sub

Default: withTime= False
20210414113752 14-Apr-21

WithTime set to True

Sub testUpsdateTime()
      Dim s As String
10    s = "20210414113752"
20    Debug.Print s, Convert2Date(s, True)
End Sub

20210414113752 14-Apr-21 11:37:52 AM