0

I have a spreadsheet full of data with dates and times in cells, but they're formatted as text and I can't just reformat them to dates (at least not without double-clicking on each cell for some reason).

The text strings look like this:

10/1/2015 4:32:16 PM

Any idea how to get it into a regular date format without the time? The ideal format is just mm/dd/yyyy. I've already tried extracting the text with =DATE(Left(F3,9)) or something similar.

FMpro
  • 17
  • 6
  • 1
    Does `DATEVALUE` not work? – lebelinoz Sep 12 '17 at 11:58
  • 1
    [Have a look at my answer here. This will convert text to a date and then you can do what you want. Format it to remove the time visually or use a formula](https://stackoverflow.com/a/46083860/3042759) – Tom Sep 12 '17 at 12:06
  • You might want to read through this to better understand dates / times in Excel: http://stackoverflow.com/a/37101358/1153513 This is probably also a good read: http://stackoverflow.com/a/38001028/1153513 Afterwards, please check if these are true dates (just looking at a cell doesn't tell you anything) and how you want to fix the "dates": with VBA or with Excel formulas. – Ralph Sep 12 '17 at 12:12

1 Answers1

3

You should select whole date column, then go to Data tab ==> Text to Columns, choose delimited type and press next until you get to final step. Select Date formatting and click finish. This will replace every input from text to date, without the need of double clicking on each entry.

After that you can simply select all cells, go to format cells and choose date formatting you want.

Kresimir L.
  • 2,301
  • 2
  • 10
  • 22