Combining Mid Right Left with DATE function

Hi,

I am looking to combine these two functions but it does not seem to be working...

the beginning of my F3 cell is:
04/01/2010

04 represents month, 01 represents day, 2010 represents year

I'd like to pull that out into a date format in another cell

my formula I have is : =date(right(F3,4)|left(F3,2)|mid(F3,2)) but it doesn't work

Does anyone have an idea why?

Do you mean:

Formula:

Please Login or Register  to view this content.

Hi,

I am not sure what you mean by a UK string date...but it seems like that is the problem as I am getting years in the 2085.

I've attached the file (it starts 01/04/2010 which is April 1, 2010) and it is in chron. order.

Am I allowed to bump threads?

Your dates are already formated as dates.
So if you want change date formatting you just write =B3 and format cell as dd.mm.yyyy or mm.dd.yyyy or m.d.yy or whatever you like.

Look at row 974 for example

Before that, Excel thinks it is DATE format, after that Excel puts it at GENERAL format.

so in row 974 it says 13/04/10 which is supposed to represent April 13, 2010. The row before that says 12/04/10 which represents April 12, 2010. But Excel thinks that 12/04/10 must mean December 4, 2010 and then when it hits row 974 it gets messed up and confused and changes it to GENERAL format...and when you change it to DATE format nothing happens.