Amending date formats.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I have a column in a spreadsheet which shows dates. These dates are mixed
between U.S. style and UK style. What the quickes way to switch them all over
to UK style?

I have tried mass formatting the cells but this does not work and it will
take me forever to adjust them individually.

Many thanks in advance,

Rik
 
How do you know which is which? With 26/6/07 or 6/26/07 it's obvious, but
how do you know what 4/6/07 is intended to be? Can you perhaps rely on all
the ones which are actually numbers [ISNUMBER()] being UK style, and all
those which are text cells [ISTEXT()] being US (or v.v.)?
 
Hi David

There are other formulas on the sheet that show things like days outstanding.
The erraneous ones show up with things like negative number of days
outstanding and such like. Its easy to mark which ones are US dates, this has
already been done. I just need to know how to switch US to UK en masse.

David Biddulph said:
How do you know which is which? With 26/6/07 or 6/26/07 it's obvious, but
how do you know what 4/6/07 is intended to be? Can you perhaps rely on all
the ones which are actually numbers [ISNUMBER()] being UK style, and all
those which are text cells [ISTEXT()] being US (or v.v.)?
--
David Biddulph

Chiccada said:
Hi all,

I have a column in a spreadsheet which shows dates. These dates are mixed
between U.S. style and UK style. What the quickes way to switch them all
over
to UK style?

I have tried mass formatting the cells but this does not work and it will
take me forever to adjust them individually.

Many thanks in advance,

Rik
Click to expand...
Click to expand...
 
Managed to figure it out.

Got told the original data comes in as all US dates. Therefore, i just text
to columns the dates, switched the month and the day. Then i concatenated the
3 parts back together.

David Biddulph said:
How do you know which is which? With 26/6/07 or 6/26/07 it's obvious, but
how do you know what 4/6/07 is intended to be? Can you perhaps rely on all
the ones which are actually numbers [ISNUMBER()] being UK style, and all
those which are text cells [ISTEXT()] being US (or v.v.)?
--
David Biddulph

Chiccada said:
Hi all,

I have a column in a spreadsheet which shows dates. These dates are mixed
between U.S. style and UK style. What the quickes way to switch them all
over
to UK style?

I have tried mass formatting the cells but this does not work and it will
take me forever to adjust them individually.

Many thanks in advance,

Rik
Click to expand...
Click to expand...
 
You can actually do the whole thing in one step with Text to Columns. You
just need to tell it what format the incoming data is in at the final stage
of the import wizard. --
David Biddulph

Chiccada said:
Managed to figure it out.

Got told the original data comes in as all US dates. Therefore, i just
text
to columns the dates, switched the month and the day. Then i concatenated
the
3 parts back together.

David Biddulph said:
How do you know which is which? With 26/6/07 or 6/26/07 it's obvious,
but
how do you know what 4/6/07 is intended to be? Can you perhaps rely on
all
the ones which are actually numbers [ISNUMBER()] being UK style, and all
those which are text cells [ISTEXT()] being US (or v.v.)?
--
David Biddulph

Chiccada said:
Hi all,

I have a column in a spreadsheet which shows dates. These dates are
mixed
between U.S. style and UK style. What the quickes way to switch them
all
over
to UK style?

I have tried mass formatting the cells but this does not work and it
will
take me forever to adjust them individually.

Many thanks in advance,

Rik
Click to expand...
Click to expand...
Click to expand...
 
Back
Top