Format date

  • Thread starter Thread starter MAX
  • Start date Start date
M

MAX

Hello
I'm working on excel 2007 and I have a workbook with 15 sheets. The problem
is that the dates are all in English US and I want them in English UK. What
is the solution to change all the 15 sheets at once?

Thanks in advance.
 
The following small macro:

Sub fixum()
Dim sh As Worksheet
Dim r As Range, rng As Range
For Each sh In Sheets
sh.Activate
Set rng = ActiveSheet.UsedRange
For Each r In rng
If r.NumberFormat = "m/d/yyyy" Then
r.NumberFormat = "d/m/yyyy"
End If
Next
Next
End Sub

will loop over all your sheets and change cell formatted like:
12/28/2009
to:
28/12/2009

You will need to modify the code if the original format differs from the
above.

I suggest using a different approach and use something like:
28 December 2009

This is easily understood no matter which side of the Atlantic you are
standing on.
 
Back
Top