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

 

[Ron Rosenfeld]

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

You are leaving something out.

If the files you are importing are Excel files, with "real" dates, the nationality of the computer on which the file was created should not be an issue. It should already be there as a numerical value. There might be an issue, however, if some of the Excel files are using the 1904 date system.
However, if the files you are importing are text files, then you need to do the conversion using the "text to columns" or "text import" wizard.
If the file is a .txt file, opening it should bring up the text-to-columns wizard where there is a step at which you can specify the format of the date column.
If the file is a .csv file, you can either change it to a .txt file (merely by changing the suffix), or choose to "import" the file rather than open it, which should open the import wizard, giving the same functionality.

If the above does not help, please provide a more complete description of exactly what is going on.

 

[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

 

[Ron Rosenfeld]

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

Glad to help. Post back with more details if you require further assistance.

 

[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.