Date and Time values

[Frank]

I am working with a database that has date and time fields that are actually
text. The date is inthe format yyyymmdd and the time is HHmmss (24 HR). Is
there a way that can I put these two numbers together to perform date/time
calculations in Access and Excel exports?

 

[Brendan Reynolds]

Public Function Strings2Date( _
ByVal DateString As String, _
ByVal TimeString As String _
) As Date

'assumes DateString contains
'a date in yyyymmdd format
'and TimeString contnains a
'time in hhmmss format

Dim strYear As String
Dim strMonth As String
Dim strDay As String
Dim strHour As String
Dim strMinute As String
Dim strSecond As String

strYear = Left$(DateString, 4)
Debug.Print "Year: " & strYear
strMonth = Mid$(DateString, 5, 2)
Debug.Print "Month: " & strMonth
strDay = Right$(DateString, 2)
Debug.Print "Day: " & strDay
strHour = Left$(TimeString, 2)
Debug.Print "Hour: " & strHour
strMinute = Mid$(TimeString, 3, 2)
Debug.Print "Minute: " & strMinute
strSecond = Right$(TimeString, 2)
Debug.Print "Second: " & strSecond

Strings2Date = DateSerial(Int(strYear), _
Int(strMonth), Int(strDay)) + _
TimeSerial(Int(strHour), _
Int(strMinute), Int(strSecond))

End Function

Example of use, in Immediate window (note my system is using dd/mm/yyyy date
format) ...

? Strings2Date("20060902", "150101")
Year: 2006
Month: 09
Day: 02
Hour: 15
Minute: 01
Second: 01
02/09/2006 15:01:01

You can delete or comment out the lines that begin with 'Debug' if you like.
They are only used for testing and for illustrating how the code works.

 

[Douglas J. Steele]

Actually, it's even easier than that, Brendan:

Public Function Strings2Date( _
ByVal DateString As String, _
ByVal TimeString As String _
) As Date

'assumes DateString contains
'a date in yyyymmdd format
'and TimeString contnains a
'time in hhmmss format

Strings2Date = CDate(Format(DateString, _
"0000\-00\-00")) + _
CDate(Format(TimeString, _
"00\:00\:00"))

End Function

(although you don't get the visual proof you have with your Debug.Print
statements <g>)