retrieve date from string

Hi All,

I search a word document and retrieve a written date,
for example: 20 October 2008.
Now I need to get from this string the date in format YYYYMMDD.
When the string is in the right format I need to have it back as a
string like:
"20081020". So I can use it in the filename.

Marco

Larry
Sat Oct 11 13:14:30 CDT 2008


"Co" <vonclausowitz@gmail.com> wrote

> I search a word document and retrieve a written date,
> for example: 20 October 2008.
> Now I need to get from this string the date in format YYYYMMDD.
> When the string is in the right format I need to have it back as a
> string like:
> "20081020". So I can use it in the filename.


What have you tried?

LFS

Co
Sat Oct 11 13:58:25 CDT 2008

On 11 okt, 20:14, "Larry Serflaten" <serfla...@usinternet.com> wrote:
> "Co" <vonclausow...@gmail.com> wrote
>
> > I search a word document and retrieve a written date,
> > for example: 20 October 2008.
> > Now I need to get from this string the date in format YYYYMMDD.
> > When the string is in the right format I need to have it back as a
> > string like:
> > "20081020". So I can use it in the filename.
>
> What have you tried?
>
> LFS

If sDtmBestand = "" Then
sClnDate2 = Format(Now(), "YYYYMMDD")
Else
sClnDate2 = Format(sClnDate, "YYYYMMDD")
End If

I have been trying with a string and a date but I seem to get a type
mismatch.

Marco

Larry
Sat Oct 11 23:34:57 CDT 2008


"Co" <vonclausowitz@gmail.com> wrote

> If sDtmBestand = "" Then
> sClnDate2 = Format(Now(), "YYYYMMDD")
> Else
> sClnDate2 = Format(sClnDate, "YYYYMMDD")
> End If
>

If you are confident you will always have the format "20 October 2008"
(DD MMM YYYY) then you can use CDate:

Else
sClnDate2 = Format$(CDate(sClnDate), "YYYYMMDD")
End If

LFS

Co
Sun Oct 12 05:13:32 CDT 2008

On 12 okt, 06:34, "Larry Serflaten" <serfla...@usinternet.com> wrote:
> "Co" <vonclausow...@gmail.com> wrote
>
> > =A0 =A0 If sDtmBestand =3D "" Then
> > =A0 =A0 =A0 =A0sClnDate2 =3D Format(Now(), "YYYYMMDD")
> > =A0 =A0 Else
> > =A0 =A0 =A0 =A0sClnDate2 =3D Format(sClnDate, "YYYYMMDD")
> > =A0 =A0 End If
>
> If you are confident you will always have the format "20 October 2008"
> (DD MMM YYYY) then you can use CDate:
>
> =A0 Else
> =A0 =A0 sClnDate2 =3D Format$(CDate(sClnDate), "YYYYMMDD")
> =A0 End If
>
> LFS

I get a type mismatch.
Where sClnDate2 and sClnDate are strings.

Marco

Co
Sun Oct 12 05:45:15 CDT 2008

On 12 okt, 06:34, "Larry Serflaten" <serfla...@usinternet.com> wrote:
> "Co" <vonclausow...@gmail.com> wrote
>
> > =A0 =A0 If sDtmBestand =3D "" Then
> > =A0 =A0 =A0 =A0sClnDate2 =3D Format(Now(), "YYYYMMDD")
> > =A0 =A0 Else
> > =A0 =A0 =A0 =A0sClnDate2 =3D Format(sClnDate, "YYYYMMDD")
> > =A0 =A0 End If
>
> If you are confident you will always have the format "20 October 2008"
> (DD MMM YYYY) then you can use CDate:
>
> =A0 Else
> =A0 =A0 sClnDate2 =3D Format$(CDate(sClnDate), "YYYYMMDD")
> =A0 End If
>
> LFS

Larry,

I worked out following:

Public Function ConvDate(DateIn As String) As String

Dim stDate As String
Dim dDate As Date
Dim i As Integer
Const sUKMonths =3D
"January,February,March,May,June,July,August,October"
Const sNLMonths =3D
"Januari,Februari,Maart,Mei,Juni,Juli,Augustus,Oktober"
Dim sItem() As String
Dim sReplace() As String

stDate =3D Format(DateIn, "mm/dd/yyyy hh:mm")
If IsDate(stDate) Then
dDate =3D CDate(stDate)
ConvDate =3D Format(CStr(dDate), "YYYYMMDD")
Else 'try to solve date problem

sItem =3D Split(sUKMonths, ",", -1)
sReplace =3D Split(sNLMonths, ",", -1)
For i =3D 0 To UBound(sItem)
If InStr(stDate, sItem(i)) Then
stDate =3D Replace(stDate, sItem(i), sReplace(i))
dDate =3D CDate(stDate)
ConvDate =3D Format(CStr(dDate), "YYYYMMDD")
Exit For
End If
Next

End If

End Function

Now the only thing I have to find out is how to handle the regional
settings of a machine.
Is the next date: 01-03-2008 -> 1 March 2008 or 3 January 2008?

Is there anything to settle that?

Marco

Larry
Sun Oct 12 10:26:20 CDT 2008


"Co" <vonclausowitz@gmail.com> wrote

Now the only thing I have to find out is how to handle the regional
settings of a machine.
Is the next date: 01-03-2008 -> 1 March 2008 or 3 January 2008?

Is there anything to settle that?
---------------

CDate uses the localility settings of the system. I don't know why
you got a type mismatch, it works fine here....

If your text is going to be in local format you need do nothing, but
if not, you're in for a real treat.... ;-)

(There simply isn't any way to know the format other than taking a
sampling of several from the same locale and inferring the order)

LFS

Co
Sun Oct 12 12:01:05 CDT 2008

On 12 okt, 17:26, "Larry Serflaten" <serfla...@usinternet.com> wrote:
> "Co" <vonclausow...@gmail.com> wrote
>
> Now the only thing I have to find out is how to handle the regional
> settings of a machine.
> Is the next date: 01-03-2008 -> 1 March 2008 or 3 January 2008?
>
> Is there anything to settle that?
> ---------------
>
> CDate uses the localility settings of the system. =A0I don't know why
> you got a type mismatch, it works fine here....
>
> If your text is going to be in local format you need do nothing, but
> if not, you're in for a real treat.... =A0;-)
>
> (There simply isn't any way to know the format other than taking a
> sampling of several from the same locale and inferring the order)
>
> LFS
Would this be a solution? I get the right results when I try several
date formats:

Public Function ConvDate(DateIn As String) As String

Dim stDate As String
Dim dDate As Date
Dim i As Integer
Const sUKMonths =3D
"JANUARY,FEBRUARY,MARCH,MAY,JUNE,JULY,AUGUST,OCTOBER,MAR,OCT"
Const sNLMonths =3D
"JANUARI,FEBRUARI,MAART,MEI,JUNI,JULI,AUGUSTUS,OKTOBER,MRT,OKT"
Dim sItem() As String
Dim sReplace() As String
Dim sDayMonthIssue As String

DateIn =3D UCase(DateIn)
sDayMonthIssue =3D GetLocaleItem(LOCALE_USER_DEFAULT,
LOCALE_SLONGDATE)
If Left(sDayMonthIssue, 1) =3D "d" Then
stDate =3D Format(DateIn, "dd/mm/yyyy hh:mm")
Else
stDate =3D Format(DateIn, "mm/dd/yyyy hh:mm")
End If
If IsDate(stDate) Then
dDate =3D CDate(stDate)
ConvDate =3D Format(CStr(dDate), "YYYYMMDD")
Else 'try to solve date problem

sItem =3D Split(sUKMonths, ",", -1)
sReplace =3D Split(sNLMonths, ",", -1)
For i =3D 0 To UBound(sItem)
If InStr(stDate, sItem(i)) Then
stDate =3D Replace(stDate, sItem(i), sReplace(i))
dDate =3D CDate(stDate)
ConvDate =3D Format(CStr(dDate), "YYYYMMDD")
Exit For
End If
Next

End If


Marco

Larry
Sun Oct 12 14:18:29 CDT 2008


"Co" <vonclausowitz@gmail.com> wrote

Would this be a solution? I get the right results when I try several
date formats:

You indicate you search Word documents, but the confusing
part is where those dates originate from. If they are user generated
text, then your solution would be lacking for so many other locales.
If they always in one of two formats, then you could simplify your
code greatly.

It would seem, only you know the extent of which you have to
support, if what you have works for you, then it is 'a solution'....

LFS

Rick
Sun Oct 12 15:13:17 CDT 2008

I have a feeling your function can be simplified, but that depends on my
understanding something about how it is used. The one thing I am unclear on
is, for your ConvDate function, what are the possible forms the DateIn
argument can take? Is it always in the form 20 October 2008 like your
original post showed? Or could it be differently formatted (if so, how)?

And when you asked "Is the next date: 01-03-2008 -> 1 March 2008 or 3
January 2008?", how does this relate to what you are doing? I don't see
where that fits in to your function's use... it that a second question
regarding how to show a newly calculated date back to the user of your
program?

--
Rick (MVP - Excel)


"Co" <vonclausowitz@gmail.com> wrote in message
news:7aeb89fb-0f59-4c64-81b4-1eab35a700f6@m36g2000hse.googlegroups.com...
On 12 okt, 17:26, "Larry Serflaten" <serfla...@usinternet.com> wrote:
> "Co" <vonclausow...@gmail.com> wrote
>
> Now the only thing I have to find out is how to handle the regional
> settings of a machine.
> Is the next date: 01-03-2008 -> 1 March 2008 or 3 January 2008?
>
> Is there anything to settle that?
> ---------------
>
> CDate uses the localility settings of the system. I don't know why
> you got a type mismatch, it works fine here....
>
> If your text is going to be in local format you need do nothing, but
> if not, you're in for a real treat.... ;-)
>
> (There simply isn't any way to know the format other than taking a
> sampling of several from the same locale and inferring the order)
>
> LFS
Would this be a solution? I get the right results when I try several
date formats:

Public Function ConvDate(DateIn As String) As String

Dim stDate As String
Dim dDate As Date
Dim i As Integer
Const sUKMonths =
"JANUARY,FEBRUARY,MARCH,MAY,JUNE,JULY,AUGUST,OCTOBER,MAR,OCT"
Const sNLMonths =
"JANUARI,FEBRUARI,MAART,MEI,JUNI,JULI,AUGUSTUS,OKTOBER,MRT,OKT"
Dim sItem() As String
Dim sReplace() As String
Dim sDayMonthIssue As String

DateIn = UCase(DateIn)
sDayMonthIssue = GetLocaleItem(LOCALE_USER_DEFAULT,
LOCALE_SLONGDATE)
If Left(sDayMonthIssue, 1) = "d" Then
stDate = Format(DateIn, "dd/mm/yyyy hh:mm")
Else
stDate = Format(DateIn, "mm/dd/yyyy hh:mm")
End If
If IsDate(stDate) Then
dDate = CDate(stDate)
ConvDate = Format(CStr(dDate), "YYYYMMDD")
Else 'try to solve date problem

sItem = Split(sUKMonths, ",", -1)
sReplace = Split(sNLMonths, ",", -1)
For i = 0 To UBound(sItem)
If InStr(stDate, sItem(i)) Then
stDate = Replace(stDate, sItem(i), sReplace(i))
dDate = CDate(stDate)
ConvDate = Format(CStr(dDate), "YYYYMMDD")
Exit For
End If
Next

End If


Marco

Co
Sun Oct 12 16:02:44 CDT 2008

On 12 okt, 22:13, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> I have a feeling your function can be simplified, but that depends on my
> understanding something about how it is used. The one thing I am unclear =
on
> is, for your ConvDate function, what are the possible forms the DateIn
> argument can take? Is it always in the form 20 October 2008 like your
> original post showed? Or could it be differently formatted (if so, how)?
>
> And when you asked "Is the next date: 01-03-2008 -> 1 March 2008 or 3
> January 2008?", how does this relate to what you are doing? I don't see
> where that fits in to your function's use... it that a second question
> regarding how to show a newly calculated date back to the user of your
> program?
>
> --
> Rick (MVP - Excel)
>
> "Co" <vonclausow...@gmail.com> wrote in message
>
> news:7aeb89fb-0f59-4c64-81b4-1eab35a700f6@m36g2000hse.googlegroups.com...
> On 12 okt, 17:26, "Larry Serflaten" <serfla...@usinternet.com> wrote:
>
> > "Co" <vonclausow...@gmail.com> wrote
>
> > Now the only thing I have to find out is how to handle the regional
> > settings of a machine.
> > Is the next date: 01-03-2008 -> 1 March 2008 or 3 January 2008?
>
> > Is there anything to settle that?
> > ---------------
>
> > CDate uses the localility settings of the system. I don't know why
> > you got a type mismatch, it works fine here....
>
> > If your text is going to be in local format you need do nothing, but
> > if not, you're in for a real treat.... ;-)
>
> > (There simply isn't any way to know the format other than taking a
> > sampling of several from the same locale and inferring the order)
>
> > LFS
>
> Would this be a solution? I get the right results when I try several
> date formats:
>
> Public Function ConvDate(DateIn As String) As String
>
> =A0 =A0 Dim stDate As String
> =A0 =A0 Dim dDate As Date
> =A0 =A0 Dim i As Integer
> =A0 =A0 Const sUKMonths =3D
> "JANUARY,FEBRUARY,MARCH,MAY,JUNE,JULY,AUGUST,OCTOBER,MAR,OCT"
> =A0 =A0 Const sNLMonths =3D
> "JANUARI,FEBRUARI,MAART,MEI,JUNI,JULI,AUGUSTUS,OKTOBER,MRT,OKT"
> =A0 =A0 Dim sItem() As String
> =A0 =A0 Dim sReplace() As String
> =A0 =A0 Dim sDayMonthIssue As String
>
> =A0 =A0 DateIn =3D UCase(DateIn)
> =A0 =A0 sDayMonthIssue =3D GetLocaleItem(LOCALE_USER_DEFAULT,
> LOCALE_SLONGDATE)
> =A0 =A0 If Left(sDayMonthIssue, 1) =3D "d" Then
> =A0 =A0 =A0 =A0stDate =3D Format(DateIn, "dd/mm/yyyy hh:mm")
> =A0 =A0 Else
> =A0 =A0 =A0 =A0stDate =3D Format(DateIn, "mm/dd/yyyy hh:mm")
> =A0 =A0 End If
> =A0 =A0 If IsDate(stDate) Then
> =A0 =A0 =A0 =A0 dDate =3D CDate(stDate)
> =A0 =A0 =A0 =A0 ConvDate =3D Format(CStr(dDate), "YYYYMMDD")
> =A0 =A0 Else 'try to solve date problem
>
> =A0 =A0 =A0 =A0 sItem =3D Split(sUKMonths, ",", -1)
> =A0 =A0 =A0 =A0 sReplace =3D Split(sNLMonths, ",", -1)
> =A0 =A0 =A0 =A0 For i =3D 0 To UBound(sItem)
> =A0 =A0 =A0 =A0 =A0 =A0 If InStr(stDate, sItem(i)) Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0stDate =3D Replace(stDate, sItem(i), sRepl=
ace(i))
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0dDate =3D CDate(stDate)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ConvDate =3D Format(CStr(dDate), "YYYYMMDD=
")
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Exit For
> =A0 =A0 =A0 =A0 =A0 =A0 End If
> =A0 =A0 =A0 =A0 Next
>
> =A0 =A0 End If
>
> Marco

The DateIn can take a lot of forms. That is my main problem.
It depends of course where the document is coming from.
Unfortunately we didn't manage to create a global way of writing a
date.
Sometimes it starts with the year, sometimes with the month and
sometimes
with the day. The easiest is when the month is like Oct or October.
When everything is in numbers, there's no way to find out what is
what.
08-08-07?

Eduardo
Tue Oct 14 02:32:29 CDT 2008

"Co" <vonclausowitz@gmail.com> escribió en el mensaje
news:997d844a-786e-4e6d-98a0-

> The DateIn can take a lot of forms. That is my main problem.
> It depends of course where the document is coming from.
> Unfortunately we didn't manage to create a global way of writing a
> date.
> Sometimes it starts with the year, sometimes with the month and
> sometimes
> with the day. The easiest is when the month is like Oct or October.
> When everything is in numbers, there's no way to find out what is
> what.
> 08-08-07?

I didn't read all the thread, but what i see is that you are receiving dates
in different formats (different locales), and you have to interpret these
dates.

I think there is no way if you don't have the locale information along with
the date.
Where are you receiving these dates from? E-mails?
May be looking to the IP of the e-mails, converting the IP to counties, and
then interpret the date with the locale setting of that country.