convert US dates to UK dates

[Labuscs]

I am trying to populate a pivot with expiery dates presented in US fomat
mm/dd/yyyy. Due to default settings, which I am unable to change, it
currently reads the dates as UK dates dd/mm/yyyy, and therefore presents
those with mm value > 12 as text. I used
=DATE(RIGHT(J10,4),LEFT(J10,2),MID(J10,4,2)) on the second scenario, and it
then presents these dates in UK format, but now this doesn't work on the
dates with month values < 12.

Any help on this will be appreciated.

 

[Pete_UK]

You could try something like this:

=IF(ISNUMBER(J10),DATE(YEAR(J10),DAY(J10),MONTH(J10)),DATE(RIGHT(J10,4),LEFT(J10,2),MID(J10,4,2)))

So, if it has already been recognised as a date then swap the day and
the month around, otherwise use your formula.

Hope this helps.

Pete

 

[Dave Peterson]

I think you may have more problems than you think.

If you imported the data from a text file and some of the data got converted to
dates and some didn't get converted, then the stuff that is really a date isn't
correct.

If you brought 02/03/2004 into your workbook, it may be a date, but it may not
be what the original data represented. I'd give the imported date column(s) an
unambiguous date format and double check some of the entries against what the
original text file had.

I think you'll find that it's time to import the text file once again without
letting excel guess how that field should be treated.