How do I change following date/time format - 30/11/2017 01:00 AM Australia/Brisbane to mm/dd/yy?

[rstuart]

How do I change following date/time format - 30/11/2017 01:00 AM Australia/Brisbane to mm/dd/yy?

I have a big table with a column exporting the date time and location as above - but I just want to bring out mm/dd/yy

 

[Michael M]

In an adjacent column, use the formula

=LEFT(A1,FIND(" ",A1,1))*1

Then Right click >>format cells>>>Custom>>type in the dropdown>>mm/dd/yy

 

[Jon Peltier]

If that column that reads 30/11/2017 01:00 AM contains actual dates and times, you can simply change the number format to mm/dd/yy to get what you want.

If the column is text pretending to be dates and times, use Text to Columns on the Data tab. In the Wizard, select Delimited > Space or select Fixed Width (your choice), then change the first column format to Date DMY and skip any other columns. Click Finish and admire your dates. Format the column of dates as mm/dd/yy, because Excel might have guessed you wanted a four-digit year.