On Thu, 15 Oct 2009 09:31:10 -0700, 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


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