Date problem

[avinkris]

Hi,

I am having a spreadsheet where the U.S date format was entered in New Zealand date format. I am unable to correct it to right format.

Please see the attached spread sheet.

[AlKey]

Delete duplicate

[AlKey]

Try this formula:

in B2 and copy it down

=IFERROR(DATE(RIGHT(TEXT(LEFT(A2,FIND(" ",A2)-1),"DD/MM/YYYY"),4),MID(A2,FIND("/",A2)+1,2),LEFT(A2,FIND("/",A2)-1)),DATE(YEAR(A2),DAY(A2),MONTH(A2)))

[Cerbera]

I'm guessing you could play around with:

=DATEVALUE(MONTH(X)&"/"&DAY(X)&"/"&YEAR(X))

X being the original data cell reference.

The only problem would be determining which ones to apply it to.

[avinkris]

Thank you for your answers.

Is there a way to change the way the data filter detects the dates. It usually follows the month/day/year format. So is it possible to make it follow day/month/year format?

[AlKey]

try this one next the B column on sheet that I uploaded earlier.

=TEXT(B2,"dd/mm/yyyy")

[newdoverman]

I use the same date format that you do and I have made sure that the default date system set in the Region and Language in the Control Panel is set correctly for Month/Day/Year format.

I downloaded your file and selected the column of dates then used Text to Columns, Fixed width, and fixed the width to only include the date and not the time (didn't test to see if that matters). Selected the time part of the field and chose Do Not Import. Selected the other column and clicked on the date selection and chose MDY and clicked FINISH. All dates were converted to DMY format without affecting the dates that were already in the correct format.

Could the problem that you are having be due to the fact that the dates are a mixture of real dates (can be reduced to the date serial number by changing the formatting to GENERAL) and text dates that will not be reduced to a serial number with the format change?