excel date formatting not working

excel excel-2010
Crouzilles picture Crouzilles · Feb 14, 2015 · Viewed 88k times · Source

I have an excel sheet created by a 3rd party program.

One of the columns has dates in this format: "Jan 19, 2015 03:00:00 PM"

I would like these dates to appear in the following format: "19/01/2015"

I have selected the cell or cells, right clicked and selected "Format Cells...", chose "Date" in the category, then chose "14/03/2001" in the type, to no avail, the dates won't change.

I also tried "Custom" from the category and "dd/mm/yyyy" from the type, again, no changes at all.

The file is not protected, the sheet is editable.

Could someone explain what I could be doing wrong?

Regards Crouz

Answer

thijs picture thijs · Oct 28, 2017

The following worked for me:

  • Select the date column.
  • Go to the Data-tab and choose "Text to Columns".
  • On the first screen, leave radio button on "delimited" and click Next.
  • Unselect any delimiter boxes (everything blank) and click Next.
  • Under column data format choose Date
  • Click Finish.

Now you got date values

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More
Insert picture into Excel cell

I'm tying to generate a report with pictures, but I cannot get the pictures into a single cell. I can get the pictures to "float" around my worksheet, but I need to put them into a cell. How can I …

Read More