International date formats

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

When performing an Edit, replace on the cell value
10.09.03 (a system genereated date vale of 10th Sep 03)
replaceing the . with /, Excel (and in VBA when recorded
as a macro) sometimes changes the cell value to 09/10/03
(9th October 03). This is driving me potty and have spent
a vast amount of time on it trying to work out what Excel
is doing!!!!

My Regional settings are set fro the UK.

Any ideas anyone?
 
Mike said:
When performing an Edit, replace on the cell value
10.09.03 (a system genereated date vale of 10th Sep 03)
replaceing the . with /, Excel (and in VBA when recorded
as a macro) sometimes changes the cell value to 09/10/03
(9th October 03). This is driving me potty and have spent
a vast amount of time on it trying to work out what Excel
is doing!!!!

My Regional settings are set fro the UK.

Any ideas anyone?
Click to expand...

It probably has something to do with how the cell is formatted.

"10.09.03" is not actually a date. It is a text string. When you do
edit/replace (to change . into /), Excel will then recognise it as a date,
just as it would if you were typing this in. At this point, Excel interprets
the entry according to the regional settings - so with UK settings it will
be 10th September 2003 - and it stores this date as a serial number (37874)
as normal. How it then displays this serial number depends on the formatting
of the cell. With the format dd/mm/yy, it will be displayed as 10/09/03, but
with format mm/dd/yy you will see 09/10/03. (With a number format you would
see 37874, the serial number itself).

I hope this helps. The essential point is that interpreting the entry (to a
serial number) and displaying the serial number (as a date) are two quite
distinct operations with their own, different, rules.
 
Hi,

The macro will convert the date to US format because Excel
VBA speaks a version of English called American <g>. To
overcome this, you need a different approach entirely.

One would be like this:

Sub ConvertDates()
Dim oCell As Range
Dim dDate As Date
Dim sDate As String
For Each oCell In ActiveSheet.UsedRange
sDate = oCell.Value
If sDate Like "##.##.####" Then
sDate = Application.Substitute(sDate, ".", "/")
dDate = CDate(sDate)
oCell.Value = dDate
End If
Next

End Sub


Regards,

Jan Karel Pieterse
Excel TA/MVP
 
Sorry, but I have already tried this. If I set the cell
format to dd mmm yy the value will be 09 Oct 03. It seems
to be swapping the day value - 10 with the month value -
09.

If the cell is formatted to a serial no is still comes out
as the equivilant of 9th Oct 03.
 
How about changing your windows regional settings to have the same order as your
data. (You can always flip back when you're done.)

then do your edit|replace (. with /) and see what happens.

Then change the windows regional settings back the way you like.
 
Back
Top