Hi Guys,
I was thinking of creating a dialog box with 3 options, one for American
Date (Text), 1 for British Date (Text) and the late for any date values (Just
need to format) to display accordingly.
One of the formulas should be something like this:
-------------------------------------------------------------------------------------
Function Date_Formating(CurrDate)
Dim Date_Val As Date
Application.Volatile True
Date_Length = Len(Trim(CurrDate))
Select Case Date_Length
Case 10
Date_Val = Right(Left(CurrDate, 5), 2) & "/" _
& Left(CurrDate, 2) & "/" & Right(CurrDate, 4)
Case 8
Date_Val = Right(Left(CurrDate, 3), 1) & "/" _
& Left(CurrDate, 1) & "/" & Right(CurrDate, 4)
Case 9
If Right(Left(CurrDate, 2), 1) = "/" Then
Date_Val = Right(Left(CurrDate, 4), 2) & _
"/" & Left(CurrDate, 1) & "/" & Right(CurrDate, 4)
ElseIf Right(Left(CurrDate, 3), 1) = "/" Then
Date_Val = Right(Left(CurrDate, 4), 1) & _
"/" & Left(CurrDate, 2) & "/" & Right(CurrDate, 4)
End If
Case Else
Date_Val = "ERROR"
End Select
Date_Formating = Date_Val
End Functio
-------------------------------------------------------------------------------------
Would appreciate any pointers.
Thanks
Ray
"swiftcode" wrote:
> Hi Rick & Gary,
>
> I understand what was said. What i meant was the when people send me their
> information, usually there are a few hundred lines of data, and 1 column
> dedicated to the dates, which is generated from some program.
>
> As there are dates in the spreadsheets provided, will usually be in either
> (entirely) British or American format. hence once i identify that, then it is
> easier to determine whether dates like 3/7/2009 is either stated in (D/M/YYYY
> or M/D/YYYY), as there will ve other dates for me to eyeball and see the
> format e.g. 3/21/2009, which effectively tells me that it is "M/DD/YYYY".
>
> My apologies in not being able to better describe the senario better.
>
> It is not possible to take control and specifically ask for date formats to
> suit me as these data providers are not obligate to give me the information.
>
> Would anyone be able to help? My idea is that if i know its British format,
> then i will invoke 1 set of vbas, if its American format, another.
>
> Thanks
>
> Rgds
> Ray
>
>
> "Gary''s Student" wrote:
>
> > Try to take control of the material you are given. Ask for dates like:
> >
> > 18 January 2009
> >
> > Why select an ambiguous format when non-ambiguous formats are available??
> > --
> > Gary''s Student - gsnu200907
> >
> >
> > "swiftcode" wrote:
> >
> > > Hi Gary and Joel,
> > >
> > > Thanks.
> > >
> > > You are both right. This is a problem for me when people from different
> > > countries send me information using their preferred formats, as i need the
> > > information in date value and in British format.
> > >
> > > On the part regarding more information, usually when people send me their
> > > data, i assume that it is eithier American or British, so if it is
> > > "DD\MM\YYYY" or "D\M\YYYY" i will take it as British and if it is
> > > "MM\DD\YYYY" or "M\D\YYYY" as American.
> > >
> > > Would you fellas be able to help me on this?
> > >
> > > Thank you.
> > >
> > > Rgds
> > > Ray
> > >
> > >
> > >
> > > "Gary''s Student" wrote:
> > >
> > > > You need more information. For example 3\7\2009 might mean
> > > > March 7 2009 in format #6
> > > > or
> > > > July 3 2009 in format #3
> > > >
> > > >
> > > > --
> > > > Gary''s Student - gsnu200907
> > > >
> > > >
> > > > "swiftcode" wrote:
> > > >
> > > > > Hi all,
> > > > >
> > > > > I have a problem with some date formats where people send to me in various
> > > > > forms (text format)
> > > > > 1) "DD\MM\YYYY"
> > > > > 2) "D\MM\YYYY"
> > > > > 3) "D\M\YYYY"
> > > > > 4) "MM\DD\YYYY"
> > > > > 5) "MM\D\YYYY"
> > > > > 6) "M\D\YYYY"
> > > > >
> > > > > Is there anyway to be able to convert these to date values and be reflected
> > > > > as "DD\MM\YYY" using vba.
> > > > >
> > > > > Thank you for any help rendered in advance.
> > > > >
> > > > > Rgds
> > > > > Ray
|
Similar Threads
