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
  • 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
  • Ok, thank you for the guidance. That was actually a mistype, I meant 'wanted' meaning that I can't do that so this is my only option as I understand it. – Ack Feb 25 '20 at 22:13
  • 1
    Where do the dates in D come from? – Scott Craner Feb 25 '20 at 22:15
  • Ack. Enter = add comment not return.

    Column A = day Column B = Month Column C = Year Column D = DATE(C#,B#,A#)

    I've created a much clearer screenshot but am not sure how to edit my post or add it to this comment, working on that

     – Ack Feb 25 '20 at 22:18
  • 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
  • cybernetic.nomad: done, thank you – Ack Feb 25 '20 at 22:49
  • Scott Crane: thanks. I think I just needed someone to see the simple mistake that I was making. It happened when applying the DATE(year/month/day) function, I saw that the year is first instead of last and got in my mind that the date was fully reversed and instead of 'select the columns left to right' I simply reversed the order and selected right to left. Going too fast for my own good. After weeding this issue out, I was able to determine the other problem that I was having and able to apply a workaround. I'll post it to the other question when I have enough reputation. – Ack Feb 26 '20 at 00:02
  • 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 Sinha Feb 26 '20 at 08:32

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