Solved: Date formatting

[JZB]

Hi Guys
I am trying to compare two sets of data, but one has dates in US format and the other in International

07/02/2009 = 39851
02/07/2009 = 39996

I am trying to change the US format around.

I thought the best way would be to define the variables

month = left("a1",2)
day = mid("a1",4,6)
year = right("a1",4)

A1 being the date 07/02/2009 (not 39851)
but this does not seem to produce the desired 07, 02, 2009. instead it brings back 39, 51, 9851. i dont know why.:banghead:

therefore making it impossible to move onto the next step of pulling the variables together into the correct format.

Any ideas? stuck on this one.

Thanks

Jon

[lw22]

If you just want to change the value to make it appear like dd/mm/yyyy you could use"

=DATE(YEAR(A1),DAY(A1),MONTH(A1))

where A1 is your date.

[Bob Phillips]

How do you know when it is US format, and when world format? With say 12/13/2008 you know that must be US 13th Dec 2008, but what is 07/02/2009?

[JZB]

thats great thanks

[JZB]

its a good question xld. the only reason i know is the file is from bloomberg which is always US format

[JZB]

appreciate the help on this guys. problem solved