Date Problem

[Gerry]

I have a column containing
4/21/2004
10/7/2003
5/5/2004
Which I need to convert to UK dates.
because of the variables(4/, 10/) I am unable to use mid
etc in seperate columns.
also =TEXT(f2,"DD/MM/YY")fails to work.
Any further suggestions

 

[JulieD]

Hi Gerry

what is the current formatting of the cells? (right mouse click on cells,
choose format / cells ... see what's selected) ... additionally, what is
shown when you choose "general" under format cells?

this information will give us an idea of what course of action to suggest.

Cheers
JulieD

 

[Guest]

Thanks Julie.
the format is now date (ie dd/mm/yy) it was originall a
downloaded dif file saved as a xls. (Original dif now in
the ether!!)
Under format general still displays the same for all the
column.
some variables below

4/21/2004
5/5/2004
15/17/2004

 

[Laura C]

if it's in text format just use the following to add
zero's, and then another formula to switch them round.

I had the orginal date in G4
Then in G5 I put
=IF(MID(G4,2,1)="/","0"&G4,G4)
this adds a zero to make it mm

then in the column next to this (i.e. G6 for me) put
=IF(MID(H4,5,1)="/",LEFT(H4,3)&"0"&RIGHT(H4,6),H4)
this is based on the new one you just calculated

therefore this is mm/dd/yyyy

Then maybe the previous suggestions will work

Laura

 

[Gerry]

Laura,
You are nearly a darling!!
Thank YOU
(by inserting 4 columns and using mid to correct for dd /
mm/ yy in 3 an concatenate!!) Resolved
Thank you.
Only need to paste down 42,000 entries.
Kool
HAPPY FRIDAY