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.

You could try something like this:

=IF(ISNUMBER(J10),DATE(YEAR(J10),DAY(J10),MONTH(J1 0)),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

On Jul 29, 11:31*am, Labuscs
wrote:
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.

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.

Labuscs wrote:

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.

--

Dave Peterson