International Date formatting

[Daniel]

Hi,
I'm trying to add some days build a macro that adds days to a date
from an existing excel file.
However, the excel file may come from multiple sources and the way
they organize the date may come in different formats (dd/mm/yyyy or mm/
dd/yyyy). When I open the file in Excel (mine is configured to be mm/
dd/yyyy) it automatically assumes that format and the ones that do not
fit are changed to fit.

Is there a way for Excel to transform the date into its numerical
value before it changes the format independently of the format the
user used (and sometimes the date itself) so I can manipulate the
information?

Thanks in advance,

Daniel F. Uribe

 

[isabelle]

hi,

Sub Macro1()
For Each c In Range("A1:A10")
If IsDate(c) Then
' MsgBox "it is a true date"
Else
Range(c.Address) = DateSerial(Right(c, 4), Mid(c, 4, 2), Left(c, 2))
End If
Next
End Sub

 

[Daniel]

hi,

Sub Macro1()
For Each c In Range("A1:A10")
  If IsDate(c) Then
    ' MsgBox "it is a true date"
  Else
   Range(c.Address) = DateSerial(Right(c, 4), Mid(c, 4, 2), Left(c,2))
  End If
Next
End Sub

Thanks for the help Isabelle, but I think I did not explained my
problem well.

I am receiving a file with the format (dd/mm/yyyy) but it may be open
by a computer that uses the same format for date or can use (mm/dd/
yyyy).

Here's an example of what may happen with a date if it is opened by a
computer with the format (dd/mm/yyyy) or with (mm/dd/yyyy)

Original Format (dd/mm/yyyy) (mm/dd/yyyy)
6/11/2012 41219 41071
2/11/2012 41215 40950
2/11/2012 41215 40950
13/11/2012 41226 #VALUE!
14/11/2012 41227 #VALUE!
1/11/2012 41214 40919

As it can be seen the numerical value of the date changes depending on
what computer opens it, and in some cases it may not consider the date
correct while in other cases it simply changes it.

What I want to know, is if it is possible when a file is received, to
interpret the dates by its numerical value directly so that there is
no chance to misinterpret the information (without previously knowing
what the original format of the date was).

Thanks,

Daniel

 

[dfuribe]

Thank you Ron, that seemed to be the problem, Text disguising as a date. I'll see what I can do to work around it.

Daniel

 

[witek]

Here's an example of what may happen with a date if it is opened by a
computer with the format (dd/mm/yyyy) or with (mm/dd/yyyy)

Original Format (dd/mm/yyyy) (mm/dd/yyyy)
6/11/2012 41219 41071
2/11/2012 41215 40950
2/11/2012 41215 40950
13/11/2012 41226 #VALUE!
14/11/2012 41227 #VALUE!
1/11/2012 41214 40919

So it is not an Excel file.

Probably you try to import text file, and depending on local settings
first date will be interpreted as June 11th or November 6th.