Need to re-arrange a field

  • Thread starter Thread starter Gerry
  • Start date Start date
G

Gerry

We entered about 1000 records including a date column (entered as text) but
in the wrong order..

it was entered as ..... dd/mm/yyyy .... e.g. 10/06/1886

I really want it to be ... yyyy/mm/dd.. e.g. 1886/06/10

anyone have a formulae to convert it to an adjoining column in correct
order?

Many thanks

Gerry
 
If it was entered as a date and not as text, then all you need to do
is highlight the column, then click on Format | Cells | Number tab and
choose Custom from the list. Then in the box just type your format
string yyyy/mm/dd and click OK.

If they are text values then it could be done with a combination of
RIGHT, MID and LEFT functions, but let's see if the above option works
first.

Hope this helps.

Pete
 
Thank you Pete but we avoided entering it as text since it is Genealogy
data and of course Excel has problems with old/new dates.
It is text for sure.

Thanks
Gerry
 
OOPS that should have read - we avoided entering it as DATE format because
of the problems in EXCEL with old/new dates.

Gerry
 
Pete

Highly unlikely real dates unless OP is using 1904 date system.

Gerry......try this formula

=RIGHT(A1,4) & MID(A1,3,4) & LEFT(A1,2)


Gord Dibben MS Excel MVP
 
Yeah, I've only just spotted the 1886 in the post - must pay more
attention !! <bg>

To Gerry - Gord's formula assumes you have entered dates with leading
zeros for the day and month part, as you indicated earlier.

Pete
 
If you haven't entered leading 0's... it's a bit more complicated but
still doable

=right(A1,4) will always grab the year (assuming your data doesn't go
back prior to 1000... If it does, read below

=SUBSTITUTE(LEFT(A1,2),"/","") will return the day and strip out the
"/"s

The tricky part is the month

=SUBSTITUTE(IF(MID(A1,2,1)="/",MID(A1,3,2),MID(A1,4,2)),"/","")

should work... I would suggest, for ease of use, putting these in
three columns and then concatenating (=A2&"/"&A3&"/"&A4) in a fifth
column... then you can always go back and paste values to make it all
permanent (so that your spreadsheet isn't constantly trying to do
these lookups and text slices)

alternatively, if you really wanted to have it in one place, this
should work

=right(A1,4)&"/"&SUBSTITUTE(IF(MID(A1,2,1)="/",MID(A1,3,2),MID(A1,4,2)),"/","")&"/"&SUBSTITUTE(LEFT(A1,2),"/","")

should work

If your genealogy data is prior to 1000, you can use the substitute in
the first section to strip out the "/"s
 
Back
Top