0

I pasted data into my Excel and the dates are not working, they work on some rows but not on others as seen from embedded screenshot.

I'm conducting av event study based of data i've sourced from a terminal and pasted it into my excel from an US based Excel terminal so the formatting is US and not EU/Finnish as I might need it.

I've tried:

  • Text to columns.
  • Copy empty cell and paste into dates.
  • =DATEVALUE.
  • Played around in Custom Number Formatting a fair bit.
  • Copying date info into Notepad, Pasting that data into a fresh Excel file and then try to parce the date into using text to columns.

Nothing works, it seems like the source formatting is following the pasted dates, which is strange since I thought I would delete any traces of formatting by pasting into a text format in Notepad.. there is obviously something regarding the dates formatting i'm not familiar with.

If anyone knows what i'm missing, please enlighten me.

Dates in my Excel file

BigBen
  • 46,229
  • 7
  • 24
  • 40
32kode
  • 3
  • 2
  • Could you add the plain text from Notepad for reference? Are you sure the target cells were all formatted the same before you pasted the text in them? Pasting plain text will not change the format of the cells. – Christopher Hamkins Nov 19 '21 at 17:24
  • **Before** you paste the data, you **must** tell your target worksheet that the dates are in `MDY` order, and not `DMY` order which is the default for your system. Since you are copy/pasting, look for the option to `Paste using Text Import Wizard` and you will be able to do this. – Ron Rosenfeld Nov 19 '21 at 20:43
  • Thank you Ron! Staying up to 2 am was worth it! I had to go and reactivate the Legacy Wizard, will never understand why they deactivated it. – 32kode Nov 19 '21 at 22:28

0 Answers0