Using dateserial to convert text string to dates

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a field that is populated with a text string and I want to convert
this to a date. Anexample of the string is:

1192008

It is in fact a date which should be 01/19/2008 and should be formatted as
mm/dd/yyyy

I have used the Dateserial function as advised by Ken Snell and this is my
interpretation of what he has suggested

=DateSerial(CInt(Left([incdate],2)),CInt(Right([incdate],4)),CInt(Mid([incdate],2,2)))

where incdate is the field holding the data.

However this equation gives me 04/19/178 I wondered whether the proble could
be that some of the records have a date which is a two digit month such as

11142008 which is then calculated as 11/04/178 where the control is
formatted as mm/yy/dddd

Can someone help please?
Thanks
Tony
 
Also tried this
DateSerial(CInt(Left(incdate, 2)), CInt(Mid(incdate, 3,
2))),CInt(Right(incdate, 4))

But still doesn't give me correct date?
 
Actually,
=DateSerial(CInt(Left([incdate],2)),CInt(Right([incdate],4)),CInt(Mid([incdate],2,2)))
is incorrect.

DateSerial expects three arguments: year, month and day, in that order.
You're attempting to pass it month, year and day. At least, you would be if
your third argument was correct: you're picking the 2nd and 3rd positions,
which would be correct for 1192008, but then your month would be wrong
(because you're taking the left-most 2 positions)

What would 1 Jan, 2008 be: 112008 or 1012008? If it would be 112008, you've
got problems: would 1222008 be 22 Jan, 2008 or 2 Dec, 2008?

Assuming it would be 1012008, you can change the string to 8 digits using:

Right("00000000" & [incdate], 8)

Now you can use DateSerial:

DateSerial(CInt(Right(Right("00000000" & [incdate], 8), 4)),
CInt(Left(Right("00000000" & [incdate], 8), 2)), CInt(Mid(
Right("00000000" & [incdate], 8), 3, 2)))

Assuming your ShortDate format in Windows is set to mm/dd/yyyy, you can
format the corrected date so that it includes slashes, and use the CDate
function on it:

CDate(Format(Right("00000000" & [incdate], 8), "##/##/####"))
 
Tony Williams said:
I have a field that is populated with a text string and I want to convert
this to a date. Anexample of the string is:

1192008

It is in fact a date which should be 01/19/2008 and should be formatted as
mm/dd/yyyy

I have used the Dateserial function as advised by Ken Snell and this is my
interpretation of what he has suggested

=DateSerial(CInt(Left([incdate],2)),CInt(Right([incdate],4)),CInt(Mid([incdate],2,2)))

where incdate is the field holding the data.

However this equation gives me 04/19/178 I wondered whether the proble
could
be that some of the records have a date which is a two digit month such as

11142008 which is then calculated as 11/04/178 where the control is
formatted as mm/yy/dddd

Can someone help please?
Thanks
Tony
Click to expand...


What about the day? Does that also vary between one and two digits? If so,
what determines whether 1112008 is 1/11/2008 or 11/1/2008?

If the day part doesn't vary, if it is always two digits, then I believe
something like this should work ...

Public Function ParseDate(ByVal strDate As String) As Date

Dim strYear As String
Dim strDay As String
Dim strMonth As String

strYear = Right$(strDate, 4)
If Len(strDate) = 7 Then
'1-digit month
strDay = Mid$(strDate, 2, 2)
strMonth = Left$(strDate, 1)
Else
'2-digit month
strDay = Mid$(strDate, 3, 2)
strMonth = Left$(strDate, 2)
End If

ParseDate = DateSerial(Int(strYear), Int(strMonth), Int(strDay))

End Function

Examples of use in the immediate window (note my system is using dd/mm/yyyy
format) ...

? parsedate("1012008")
01/01/2008

? parsedate("11012008")
01/11/2008
 
Thank you both I need to read and digest both your replies. I'm in the Uk and
it's 18.24 so I'm calling it a day and will concentrate better with a fresh
mind in the morning. I'll post back my answers to your queries then and try
the formulae your suggesting.
Thanks again
Tony

Tony Williams said:
Also tried this
DateSerial(CInt(Left(incdate, 2)), CInt(Mid(incdate, 3,
2))),CInt(Right(incdate, 4))

But still doesn't give me correct date?

Tony Williams said:
I have a field that is populated with a text string and I want to convert
this to a date. Anexample of the string is:

1192008

It is in fact a date which should be 01/19/2008 and should be formatted as
mm/dd/yyyy

I have used the Dateserial function as advised by Ken Snell and this is my
interpretation of what he has suggested

=DateSerial(CInt(Left([incdate],2)),CInt(Right([incdate],4)),CInt(Mid([incdate],2,2)))

where incdate is the field holding the data.

However this equation gives me 04/19/178 I wondered whether the proble could
be that some of the records have a date which is a two digit month such as

11142008 which is then calculated as 11/04/178 where the control is
formatted as mm/yy/dddd

Can someone help please?
Thanks
Tony
Click to expand...
Click to expand...
 
Hi Brendan
Having looked at the data the day is always 2 digits, the month can be 1 or
3 digits and the year is always 4 digits.

So 1st January 2009 is 1012009
In your example 1112008 would be 11th January 2008

This suggests that your code should work. However not being a VBA expert I'm
not sure how I would call the procedure. I have created a control on my form
called newincdate and added =Parsedate in the BeforeUpdate event of that
control but nothing happens. Could you help?
Thanks again.
Tony

Brendan Reynolds said:
Tony Williams said:
I have a field that is populated with a text string and I want to convert
this to a date. Anexample of the string is:

1192008

It is in fact a date which should be 01/19/2008 and should be formatted as
mm/dd/yyyy

I have used the Dateserial function as advised by Ken Snell and this is my
interpretation of what he has suggested

=DateSerial(CInt(Left([incdate],2)),CInt(Right([incdate],4)),CInt(Mid([incdate],2,2)))

where incdate is the field holding the data.

However this equation gives me 04/19/178 I wondered whether the proble
could
be that some of the records have a date which is a two digit month such as

11142008 which is then calculated as 11/04/178 where the control is
formatted as mm/yy/dddd

Can someone help please?
Thanks
Tony
Click to expand...


What about the day? Does that also vary between one and two digits? If so,
what determines whether 1112008 is 1/11/2008 or 11/1/2008?

If the day part doesn't vary, if it is always two digits, then I believe
something like this should work ...

Public Function ParseDate(ByVal strDate As String) As Date

Dim strYear As String
Dim strDay As String
Dim strMonth As String

strYear = Right$(strDate, 4)
If Len(strDate) = 7 Then
'1-digit month
strDay = Mid$(strDate, 2, 2)
strMonth = Left$(strDate, 1)
Else
'2-digit month
strDay = Mid$(strDate, 3, 2)
strMonth = Left$(strDate, 2)
End If

ParseDate = DateSerial(Int(strYear), Int(strMonth), Int(strDay))

End Function

Examples of use in the immediate window (note my system is using dd/mm/yyyy
format) ...

? parsedate("1012008")
01/01/2008

? parsedate("11012008")
01/11/2008
Click to expand...
 
Sorry Brendan have just tried Douglas's suggestion and that worked!
Thanks again for the input though.
Tony

Brendan Reynolds said:
Tony Williams said:
I have a field that is populated with a text string and I want to convert
this to a date. Anexample of the string is:

1192008

It is in fact a date which should be 01/19/2008 and should be formatted as
mm/dd/yyyy

I have used the Dateserial function as advised by Ken Snell and this is my
interpretation of what he has suggested

=DateSerial(CInt(Left([incdate],2)),CInt(Right([incdate],4)),CInt(Mid([incdate],2,2)))

where incdate is the field holding the data.

However this equation gives me 04/19/178 I wondered whether the proble
could
be that some of the records have a date which is a two digit month such as

11142008 which is then calculated as 11/04/178 where the control is
formatted as mm/yy/dddd

Can someone help please?
Thanks
Tony
Click to expand...


What about the day? Does that also vary between one and two digits? If so,
what determines whether 1112008 is 1/11/2008 or 11/1/2008?

If the day part doesn't vary, if it is always two digits, then I believe
something like this should work ...

Public Function ParseDate(ByVal strDate As String) As Date

Dim strYear As String
Dim strDay As String
Dim strMonth As String

strYear = Right$(strDate, 4)
If Len(strDate) = 7 Then
'1-digit month
strDay = Mid$(strDate, 2, 2)
strMonth = Left$(strDate, 1)
Else
'2-digit month
strDay = Mid$(strDate, 3, 2)
strMonth = Left$(strDate, 2)
End If

ParseDate = DateSerial(Int(strYear), Int(strMonth), Int(strDay))

End Function

Examples of use in the immediate window (note my system is using dd/mm/yyyy
format) ...

? parsedate("1012008")
01/01/2008

? parsedate("11012008")
01/11/2008
Click to expand...
 
Thanks Douglas, that worked!

Having looked at the data the day is always 2 digits, the month can be 1 or
3 digits and the year is always 4 digits.

In your example 1 Jan 2008 would be would be 1012008 and 1222008 would be 22
Jan 2008


Douglas J. Steele said:
Actually,
=DateSerial(CInt(Left([incdate],2)),CInt(Right([incdate],4)),CInt(Mid([incdate],2,2)))
is incorrect.

DateSerial expects three arguments: year, month and day, in that order.
You're attempting to pass it month, year and day. At least, you would be if
your third argument was correct: you're picking the 2nd and 3rd positions,
which would be correct for 1192008, but then your month would be wrong
(because you're taking the left-most 2 positions)

What would 1 Jan, 2008 be: 112008 or 1012008? If it would be 112008, you've
got problems: would 1222008 be 22 Jan, 2008 or 2 Dec, 2008?

Assuming it would be 1012008, you can change the string to 8 digits using:

Right("00000000" & [incdate], 8)

Now you can use DateSerial:

DateSerial(CInt(Right(Right("00000000" & [incdate], 8), 4)),
CInt(Left(Right("00000000" & [incdate], 8), 2)), CInt(Mid(
Right("00000000" & [incdate], 8), 3, 2)))

Assuming your ShortDate format in Windows is set to mm/dd/yyyy, you can
format the corrected date so that it includes slashes, and use the CDate
function on it:

CDate(Format(Right("00000000" & [incdate], 8), "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Tony Williams said:
I have a field that is populated with a text string and I want to convert
this to a date. Anexample of the string is:

1192008

It is in fact a date which should be 01/19/2008 and should be formatted as
mm/dd/yyyy

I have used the Dateserial function as advised by Ken Snell and this is my
interpretation of what he has suggested

=DateSerial(CInt(Left([incdate],2)),CInt(Right([incdate],4)),CInt(Mid([incdate],2,2)))

where incdate is the field holding the data.

However this equation gives me 04/19/178 I wondered whether the proble
could
be that some of the records have a date which is a two digit month such as

11142008 which is then calculated as 11/04/178 where the control is
formatted as mm/yy/dddd

Can someone help please?
Thanks
Tony
Click to expand...
Click to expand...
 
Back
Top