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

9
  • 2
    "which I want this to be a response to" -- note we're not a forum, so posting questions to an existing question is not allowed. That said: please make clear what's the input and what's the result in your screenshot. Did you consider that column A is interpreted as the day and B as the (overflowing) month? – Arjan Feb 25 '20 at 22:09
  • 1
    Where do the dates in D come from? – Scott Craner Feb 25 '20 at 22:15
  • 1
    There's a link just below your post, right underneath the tags – cybernetic.nomad Feb 25 '20 at 22:38
  • 3
    Umm well when you do =DATE(2020,31,1) it returning the 1st in the 31st month of the 2020 year. So the 31st month is 2 years and 7 months added to 1/1/2020 you sure you do not want =DATE(C2,A2,B2) where the Month is actually column A and the day is column B. to me the formula is returning the correct value. – Scott Craner Feb 25 '20 at 22:43
  • 2
    @Ack , in fact you did the basic mistake,,, where Month value are more that 12, you are getting unexpected Date,, considering 01/31/2020 returns 07/01/2022 ,reason is that Excel add 19 to moths, if U use this =DATE(YEAR(C2),MONTH(B2)-19,DAY(A2)) in D2, you get 12/01/2020 , hope this help you ! – Rajesh S Feb 26 '20 at 8:32
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.