Batch Edit Cells

You need to convert them to the real dates. Use this formula in B2 and copy down then format as Date.

Formula:

Please Login or Register  to view this content.

Correction. Didn't notice that there are "EDT"

Use this

Formula:

Please Login or Register  to view this content.

Hello,

Excel regards this data as text, not as dates. You need to convert it to real date/time values.

If you have data to the right of that column, insert two empty columns. Then select the column, then click Data > Text to columns

- tick "Fixed width" > Next
- double click the arrow line between the time and the PM to remove it and click Next
- with the first column highlighted, select the Date format option and the MDY order from the dropdown.
- click Finish

Now you have three columns. You can now create a formula to add the date and time columns to arrive at a single date/time value for each row.

=a2+b2

Format this column to show date and time. It can now be sorted by date.

cheers, teylyn

Al, that only works with specific regional settings. If the computer is set to DMY (like mine) it fails.

Another quick option that will only work if the dates have the same format as the regional settings: Find and Replace.

- hit Ctrl-H
- in Find What enter a space and the EST
- leave Replace with blank
- click Replace All.

Do the same for EDT and any other time zone markings you have.

The RIGHT(A2,4) part will remove the last 4 characters " EST" and " EDT" to allow conversion from text to numeric values.