Help converting date format in excel 16.54

[jwnorris]

I have a list fo approximately 300 dates that are formatted incorrectly. Some are dd-mm-yyyy and others are dd/mm/yyyy. I need to convert to mm/dd/yyyy in mass 9not one at a time). The month is in the middle.

Can anyone help?

 

[hg.wells]

You should be able to use Find and Replace, Cmd+F. Find all the - and replace with /

 

[joevt]

Assuming your dates are in a text file, one date per line
1) In the text file, Select All and Copy.
2) In Excel, Paste. Click the Clipboard icon that appears at the bottom right of the newly pasted cells, select Text Import Wizard..., click Next>, click Next> again, select Date: DMY, click Finish.
3) In Excel, select the date cells, select Format->Cells, select Number->Custom, enter Type as mm/dd/yyyy

Now all the dates should be in the mm/dd/yyyy format (except any that could not be imported because the month or day is out of range).

 

[jwnorris]

"2) In Excel, Paste. Click the Clipboard icon that appears at the bottom right of the newly pasted cells, select Text Import Wizard..., click Next>, click Next> again, select Date: DMY, click Finish."

This doesn't work. When clicking the clipboard icon, 'TEXT IMPORT WIZARD' is not an option.

 

[jwnorris]

You should be able to use Find and Replace, Cmd+F. Find all the - and replace with /

I can't use FIND and RELACE as the month is in the middle, regardless of the seperater, - or /. ddmmyyyy

 

[organicCPU]

Assuming that your dates are in column A, you could use this formula in the first row:

=MID(A1;4;2)&"/"&LEFT(A1;2)&"/"&RIGHT(A1;4)

 

[joevt]

This doesn't work. When clicking the clipboard icon, 'TEXT IMPORT WIZARD' is not an option.

Use TextEdit.app. Select Format -> Make Plain Text. Then go to step 1.

I can't use FIND and RELACE as the month is in the middle, regardless of the seperater, - or /. ddmmyyyy

A text editor like BBEdit.app allows using regular expressions (grep) for search and replace.
Search for (\d+)[-/](\d+)[-/](\d+)
replace with \2/\1/\3