I am trying to write a function that will convert a string date to a universal date. Basically to take someones screwup and fix it. But I got stumped on short dates like "M/d/yyyy"
Primarily because the position of the "d" for example, won't necessarily be there if it is a 2 digit month or 2 digit day. I am probably taking the wrong approach to this, maybe I should figure out all the separators (anything not an M, d or y), store them, and then search for them in the result string, their everything that is not a separator should be the data I want. But that's a bit too complicated for me to wrap my head around to code.
This is what I got so far if you think you can help
[code]
Private Function GetUniversalDate(ByVal sDate As String) As String
Dim sDay As String
Dim sMonth As String
Dim sYear As String
Dim iDayLocation As Integer
Dim iDayCount As Integer
Dim iMonthLocation As Integer
Dim iMonthCount As Integer
Dim iYearLocation As Integer
Dim iYearCount As Integer
Dim sReturn As String
Dim sCount() As String 'the ubound is used to determine the number of entries
Dim strResult As String
Dim strInfo As String * 10
Dim lngIdentifier As Long
Dim lngResult As Long
lngIdentifier = GetUserDefaultLCID()
lngResult = GetLocaleInfo(lngIdentifier, LOCALE_SSHORTDATE, strInfo, 10)
strResult = Replace(strInfo, Chr(0), "")
'first, the days
sCount = Split(strResult, "d")
iDayCount = UBound(sCount)
iDayLocation = InStr(strResult, "d")
'now the months
sCount = Split(strResult, "M")
iMonthCount = UBound(sCount)
If iMonthCount > 3 Then
'THis will error, throw a message and break out returning the original data
sReturn = sDate
GetUniversalDate = sReturn
Call Oops("", 10, "Error, short date format cannot contain a full month (MMMM)", "GetUniversalDate")
Exit Function
End If
iMonthLocation = InStr(strResult, "M")
'And the years
sCount = Split(strResult, "y")
iYearCount = UBound(sCount)
iYearLocation = InStr(strResult, "y")
[b]
If iDayCount = 1 And iYearLocation > iDayLocation Then
iYearLocation = iYearLocation + 1
End If
[/b]
'So, using the locations and the count, we can get the values
sDay = Mid$(sDate, iDayLocation, [b]2[/b])
[b]
If IsNumeric(sDay) = False Then 'something wrong then
sDay = Mid$(sDate, iDayLocation, 1)
If iYearLocation > iDayLocation Then
iYearLocation = iYearLocation - 1
End If
End If
[/b]
sMonth = Mid$(sDate, iMonthLocation, iMonthCount)
sYear = Mid$(sDate, iYearLocation, iYearCount)
sReturn = sYear & "-" & sMonth & "-" & sDay
sReturn = Format$(CDate(sReturn), "yyyy/MM/dd")
GetUniversalDate = sReturn
End Function
[/code]
The bold code is the code I wrote to try and handle the problems I was having, so far no luck.