American date to uk date format

[leehale]

Hello all.
I import a spreadsheet from the states and the date format needs to be formatted to UK. Instead of being MM.DD.YY I need DD.MM.YY is there an easy formatting trick I can use to change the column.

Thanks.

Lee.

 

[Livia8]

Hi Lee,

First select the cells/columns/worksheets you want to change, then right click, "format cells", click on the tab called "number", then "date" and select the date format you prefer. Alternatively, instead of "date", click on "custom", and on the blank space under "type" write DD.MM.Y, or DD/MM/YY or whatever format you want (i.e. if you use format DD MMMM YYYY and you type 15/12/02, the cell will read 15 December 2002, etc).

This should do the trick.

Regards,

Livia

 

[leehale]

Thank you Livia.
I have tried that one and it does not change. I am still lost for why it will not respond. Any other idea's?

Lee

 

[gizmo1973]

It's becasue the number is actually text just with a format put over it.
If your date is split by a full stop you could try.

text to columns on your date column split by the full stop.
Then concanate the date back to the format

there will be a beeter way with code I'm sure as my way is too clunky but I have used it before

Regards, Phil.
C.E.O. Bodgeit, Leggit & Scarper. International.
"Stuffing things up completely since 1973"

 

[Livia8]

Hi Lee,

I thought I had replied to this but something must have gone wrong 'cause I can't see my answer... I just hope it won't duplicate...


Ok, select the cells/ columns/ rows/ worksheet you need to change. Then right-click, and choose "format Cells". Then click on the "number" tab. Here you can either select "date" and pick a date format you want from the pre-exisiting list, or you go to "Custom" and in the white box under "type" you write DD.MM.YY or whatever format you need (i.e. DD MMMM YYYY would format the cells as 30 September 2004, whereas DD/MM/YY would give you 30/09/04,etc).

Hope this helps.

Regards,

Livia

 

[leehale]

07/21/03.

This is the sample date that I cannot change. If it changes here I will be happy if you can tell me how to repeat it.

Lee

 

[gizmo1973]

Try using my post but with / as your seperator in text to columns.

Regards, Phil.
C.E.O. Bodgeit, Leggit & Scarper. International.
"Stuffing things up completely since 1973"

 

[xlbo]

If it is coming down as text instead of a real date, the formula
=ISTEXT(Cell_Ref)
should return TRUE

In this case, the solution is the "Times 1 Fix"

Enter a 1 into any blank cell
Copy it
Select the "Dates"
Follow Edit>PasteSpecial and choose "Values" and Tick "Multiply"

Now format the cell as you want

et voila

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[Livia8]

Hi Lee,

I thought I had replied to this but something must have gone wrong 'cause I can't see my answer... I just hope it won't duplicate...


Ok, select the cells/ columns/ rows/ worksheet you need to change. Then right-click, and choose "format Cells". Then click on the "number" tab. Here you can either select "date" and pick a date format you want from the pre-exisiting list, or you go to "Custom" and in the white box under "type" you write DD.MM.YY or whatever format you need (i.e. DD MMMM YYYY would format the cells as 30 September 2004, whereas DD/MM/YY would give you 30/09/04,etc).

Hope this helps.

Regards,

Livia

 

[leehale]

Thanks guys.
Have managed it now by setting it as MM.DD.YY and it swapped it over. not sure why but it seems if it set it as it is it swaps it to the way my pc is set up. probably to the regedit for uk.

Anyway thank you all for you time.

Lee

 

[Livia8]

Sorry for all the duplications Lee... but I'm glad you found a solution!