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

rstuart

New Member
Joined
Apr 26, 2009
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
In an adjacent column, use the formula
Excel Formula:
=LEFT(A1,FIND(" ",A1,1))*1
Then Right click >>format cells>>>Custom>>type in the dropdown>>mm/dd/yy
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,983
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Forum statistics

Threads
1,148,284
Messages
5,745,846
Members
423,981
Latest member
ph1l

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top