Different Date formats in text to be recognised as date value

[swiftcode]

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

 

[Joel]

There is a problem with internation formats. Excel won't convert US (month -
day) to England (day - month) standard or the other way. within one country
you can easily convert using

format(DateValue("1/1/09"),"DD/MM/YYY")

 

[Gary''s Student]

You need more information. For example 3\7\2009 might mean
March 7 2009 in format #6
or
July 3 2009 in format #3

 

[swiftcode]

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

 

[Rick Rothstein]

Read Gary''s Student's reply again... how will you know which format a date
like 3/7/2009 was originally in (D/M/YYYY or M/D/YYYY)?

 

[Gary''s Student]

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??

 

[swiftcode]

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

 

[swiftcode]

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

 

[Ron Rosenfeld]

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

You indicate that these are "spreadsheets".

What do you mean by this? If these are Excel workbooks, and the dates have
been entered as "dates", then they are stored as serial numbers and you can
just convert the format of the cells to your desired "dd/mm/yyyy".

If these are text entries, into Excel cells formatted as text, then one
approach would be to loop through the range of cells, testing the first and
second sections.

If section 1 contains an entry > 12, then the dates are d/m/y format.
If section 2 contains an entry > 12, then the dates are m/d/y format

If both or neither contain an entry > 12, then the dates are indeterminate.

So something like:

================================
Option Explicit
Sub ConvertDate()
Dim rg As Range, c As Range
Dim d As Long, m As Long, y As Long
Dim dts As Variant
Dim A As Boolean, B As Boolean

'set to range where there are dates
Set rg = Selection

'loop through range to check max and min entry
'in first and second sections

For Each c In rg
dts = Split(c.Value, "/")
If dts(0) > d Then d = dts(0)
If dts(1) > m Then m = dts(1)
Next c

If d > 12 Then B = True
If m > 12 Then A = True

If A = B Then
MsgBox ("Date Format Indeterminate")
Exit Sub
End If

For Each c In rg
dts = Split(c.Value, "/")
c.NumberFormat = "dd/mm/yyyy"
If A = True Then
c.Value = DateSerial(dts(2), dts(0), dts(1))
Else
c.Value = DateSerial(dts(2), dts(1), dts(0))
End If
Next c

End Sub
==================================
--ron