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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

midnightjo@gmail.com - 08 Feb 2008 12:07 GMT

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

Reply to this Message

Niek Otten - 08 Feb 2008 12:21 GMT

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

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello,
|
[quoted text clipped - 22 lines]
| Many thanks for your help,
| Jo

Reply to this Message

midnightjo - 12 Feb 2008 09:49 GMT

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

> Hi Jo,
>
[quoted text clipped - 37 lines]
> | Many thanks for your help,
> | Jo

Reply to this Message

zerosky - 08 Feb 2008 14:48 GMT

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

> Hello,
>
[quoted text clipped - 22 lines]
> Many thanks for your help,
> Jo

Reply to this Message