Formating Dates in Excel

I think those cells are formatted as text, unfortunately.

If you convert them to dates, eg using

=DATEVALUE(A3)

Select allOpen in new window

in column B then they will format fine.

I just tried it and the cells are showing "#VALUE!"

To format all the text dates to the actual dates, do the following...
Select the whole column A --> Data --. Text to Columns --> Finish.
That's it.

I did Text to Columns -> Delimited -> Tab -> General -> Finish and its still not working.

You might have done something wrong.
After clicking on Text to Columns, you have to click on Finish in the next window without doing anything.

Watch this short demo...
https://www.screencast.com/t/lGlboHBJ09

And here is the file for your reference.
Format-problem-1.xlsx

Oh. Sorry. the #Value! comes from trying to convert dates that are already dates using DATEVALUE

If you use this:

=IF(ISNUMBER(A2),A2,DATEVALUE(A2))

Select allOpen in new window

..you should have no problem.

See attached file with dates in column B.

Try this.

1 Select column A.

2 Goto Data>Text to columns...Delimited

3 On Step 2 select Space as the delimiter.

4 On Step 3 select MDY for the Data column format of the first column and Do not import column (skip) for the other 2 columns.

5 Click Finish.

You should now have a column of 'real' dates that you can format as required.

On each of the cells that contain a text string you may see an error warning triangle in the top corner. When you select that cell there should be a dropdown next to it with an option to "Convert to number".

If you select the complete set of data from that first erroneous cell down and then use the Convert to Number option on that first cell it will convert all of those that are selected.

I have seen your file. Nothing will work to format your cell in date format because there is a special character (') (may be by mistake) already included in your cell. So remove that special character ('). Follow below steps:

• Go in to any cell which containing AM at last.
• Press F2 key.
• Remove special character ( ' ) that is first character in the cell.

After looking at your file you only need to hit F2 and then Enter on each cell that has the AM. Excel will then change the format automatically to date. I see that there are more than 2100 rows though so a script that types for you might be easiest. I use a program called AutoHotKeys. You can get it here. Then you just have to create the script. I use the number 8 key for mine like this:

:*:8::
loop,2147
{
send {f2}{enter}
}
return

Select allOpen in new window

That will hit F2 then Enter 2147 times. You cannot do anything else while it does this, but it doesn't take very long.

To create the script open Notepad and type of copy/paste the code from above and save with the file extension ".ahk".

Then where ever you saved the file go and run it. You should get an icon in your taskbar like this:
It will have an "H" when it is active. You can right click it and select "Suspend Hotkeys" to suspend it or "Exit" to close it.
While it has the "H" in the little green box go to you Excel file cell A1 and hit "8" or whatever key you put on the line :*:8::. It will start immediately and run through all the cells.

superb