Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format date
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format date
Group the sheets, then format cells.
Then ungroup the sheets to avoid confusion. -- David Biddulph MAX wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format date
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. -- Gary''s Student - gsnu200909 "MAX" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format date
Thankyou for your useful information.
*HAPPY NEW YEAR* "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200909 "MAX" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
How do I convert dd/mm/yy date format to yyddd Julian date format | Excel Worksheet Functions | |||
change date format dd/mm/yyyy to Julian date format? | Excel Worksheet Functions | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) |