Format Cells Date (or any change) not working on imported data

[midnightjo]

Hello,

I've just spent ages researching this and not come up with what I need
to be able to do.

I have a worksheet for some simple data that has been imported, a
date, text and number column (as they display graphically to the end
user). All are a "general" format when using Format > Cells.

The issue I have is that the date information is in an American date
format and I would like to change them into a UK date format. Format >
Cells and selecting any option (including custom and special) makes no
changes to the imported data.

I have seen the work arounds whereby you split out the individual day,
month and year into another cell. However this does not solve the over
riding issue that I cannot make any format change to these cells.

I've also seen the Data > Text to Columns and played with this to no
avail.

Why can't I change the cell formats on my imported data? How can I fix
this?

Many thanks for your help,
Jo

 

[Niek Otten]

Hi Jo,

Formatting doesn't help because the cell is text, not a number.
If the data is imported via a file, rename that file to a .txt file; this will give you a menu to interpret the data (like as a
date) when opening.
You can also use the Data>Text to columns command and make sure it is interpreted as a date

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hello,
|
| I've just spent ages researching this and not come up with what I need
| to be able to do.
|
| I have a worksheet for some simple data that has been imported, a
| date, text and number column (as they display graphically to the end
| user). All are a "general" format when using Format > Cells.
|
| The issue I have is that the date information is in an American date
| format and I would like to change them into a UK date format. Format >
| Cells and selecting any option (including custom and special) makes no
| changes to the imported data.
|
| I have seen the work arounds whereby you split out the individual day,
| month and year into another cell. However this does not solve the over
| riding issue that I cannot make any format change to these cells.
|
| I've also seen the Data > Text to Columns and played with this to no
| avail.
|
| Why can't I change the cell formats on my imported data? How can I fix
| this?
|
| Many thanks for your help,
| Jo

 

[zerosky]

Hi

Just spent a while trying to figure that out myself...
there's bound to be more elegant solutions but I gave up looking
after a couple of hours. This is the formulae I eventually used.

Assuming the American date is in Cell A1, copy and paste this in A2
Then reformat A2 with the UK date. ...worked for me

=DATE((RIGHT(TEXT(A1,"m/d/yyy"),4)),(MID(TEXT(A1,"m/d/yyy"),3,1)),(LEFT(TEXT(A1,"m/d/yyy"),1)))


best of luck

 

[midnightjo]

Aha! That's genius (and made me look rather intelligent at work, so
always useful!). I thought I had tried some permutation of that, but
obviously not.

Thanks very much,
Jo