0

I've been unable to resolve this and about to pull out my remaining hair. There are other similar posts such as this one which I wanted this to be a response to but I am unable to post there due to being new and yet without any earned reputation.

I've troubleshot it down to expose an error related to the month but that is as far as I have been able to get to at this point. The attached screenshot show the erroneous results

I don't want to manually retype the dates for all my data. Does anyone have any ideas how to fix this?

Some related information

  • Excel 2013
  • data was cut and paste from the web using Chrome
  • I've used many typical tricks to strip formatting
  • I used text to columns at some point
  • I've tried many solutions that I've found and none of them make a difference

screenshot of results

Ack
  • 633
  • 4
  • 12

1 Answers1

1

Use proper columns / data for the function

  • DATE(year/month/day)

I had the data reversed under the month and day column headings.

For days numbered 12 or less the error was hard to see, for day numbers greater than 12 the combined date result was increased by a year or more and easy to see.

This error occurred starting when IMPORTING the data using Get Data from Text. During that process I properly selected the Column date format radio button for Date, however, I did not update the actual format itself. Excel default was YMD while my original data was DMY. Refer to the attached screenshot. This seems to cause problems in Excel where it accepts some of the dates as expected and some of the dates will be text that will cause all kinds of additional problems. See the link in the question for more information on that. The results where that the final data was incorrectly assigned to the column title: dates and months got switched and I didn't notice

Thanks to

  • Scott Craner for noting that the data switch error
  • Rajesh S for pointing out the details of the effect of the erroneous data
Ack
  • 633
  • 4
  • 12