Excel Date Problem

[marcus]

A client has a spreadsheet which was sent from the US, and therefore a column of date entries are in the format of US type ie:

02/30/05 ie 30th feb 2005

I want these to be changed to the UK version ie:

30/02/05

However as regional settings are set to UK, Excel does not recognize 02/30/05 as a date in the first place, and does not convert it to a UK format accordingly. Also, despite formatting the column for date it still does not work.

I tried switching to a US locale, hoping that Excel would then recognize 02/30/05 as a date, and then back to UK again but this did not work.

He has several thousand entried in this format so its too much to change manually.

Any ideas?

[kernow]

a vba macro possibly to take each cells data, parse it into 3 components using / as a delimiter, then swapping the positions of 2nd with 1st

not sure how easy that would be, but I'm sure vba could do tokenizing.

might want to strip that column out and test it in its own spreadsheet first though - eep

[marcus]

VBA.. argh, entering the realms of NBI (no bloody idea) for me there... :/

[kernow]

heh I haven't done any vba since uni either

uhhm

maybe you could look around in the help for excel or get a really fat, up to date book

I'm thinking now there could be functions like .. toUKDate(); or convertDate(us,uk); or something of the sort.

maybe... obviously not with those names though .. heh

[babs]

Easiest (if longwinded) for now might be the following (assuming dates down column A).

in B1 (Months column)

=left(A1,2)

in C1 (Days column)

=mid(A1,4,2)

in D1 (Year column)

=mid(A1,7,2)

in E1 (Final date)

=concatenate(C1,"/",B1,"/",D1)

As I say, crude, but it should work.

[Bassman]

You can do the change in one formula:

=MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&MID(A1,7,2)

[marcus]

Assuming that the US date 01/30/05 is in cell A1, how do I go about applying that formula?

[Bassman]

With the US Date in column 1 you can copy and paste the formula, as I displayed it, into an adjacent cell (on the same row preferably) and it should work fine. I copied and pasted it out of a spreadsheet into the message.

[marcus]

Works for me, awaiting the response from the client now. Nice one

[mattSix]

[ignore]

[marcus]

Hmm the dates go all screwey, although they work in some cases -

date output
06/27/2005 27/06/2005
06/29/2005 29/06/2005
8/6/2005 11/04/2073
9/6/2005 01/02/2004
06/14/2005 14/06/2005
10/6/2005 03/03/2034
06/13/2005 13/06/2005
9/6/2005 01/02/2004
10/6/2005 03/03/2034
2/6/2005 29/04/2092
7/6/2005 11/03/2042
8/6/2005 11/04/2073
10/6/2005 03/03/2034
06/14/2005 14/06/2005
06/14/2005 14/06/2005
06/20/2005 20/06/2005
9/6/2005 01/02/2004
10/6/2005 03/03/2034
10/6/2005 03/03/2034

[Bassman]

Try this (assuming a 4 digit year):

=DATE(RIGHT(TRIM(A1),4),LEFT(A1,FIND("/",A1,1)-1),LEFT(MID(A1,FIND("/",A1,1)+1,3),FIND("/",MID(A1,FIND("/",A1,1)+1,3),1)-1))

[Gerry Helmet]

Why not create a custom date format MM/DD/YYYY. Then that will swap them round to what you would expect.

[Bassman]

I tried the custom format thing and couldn't get it to work.

[marcus]

Aye, seems its because Excel doesnt recognize the american-type date as a valid date format when you have UK regional selected, so treats it as text, I though that by switching to a US regional setting and then 'validating' the dates, and switching back it would fix this but it doesnt.

Bassman, it works for some but not all of them Im afriad.