retrieve date from string
Co
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 Serflaten
Co
> "Co" <vonclausow...@gmail.com> wrote
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 Serflaten
"Co" <voncla...@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
> "Co" <vonclausow...@gmail.com> wrote
I get a type mismatch.
Where sClnDate2 and sClnDate are strings.
Marco
Co
> "Co" <vonclausow...@gmail.com> wrote
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 =
"January,February,March,May,June,July,August,October"
Const sNLMonths =
"Januari,Februari,Maart,Mei,Juni,Juli,Augustus,Oktober"
Dim sItem() As String
Dim sReplace() As String
stDate = Format(DateIn, "mm/dd/yyyy hh:mm")
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
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 Serflaten
"Co" <voncla...@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
> "Co" <vonclausow...@gmail.com> wrote
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
Larry Serflaten
"Co" <voncla...@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 Rothstein
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" <voncla...@gmail.com> wrote in message
news:7aeb89fb-0f59-4c64...@m36g2000hse.googlegroups.com...
Co
<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)
>
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
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.