I eagerly read the article at https://www.myonlinetraininghub.com/change-type-using-locale-with-power-query thinking it would be an easy solution to my problem, but doesn't appear to be. Not knowing any better, I posted a question there, then realized it should have been here. So here it is. My apologies.
My February file came (from the vendor) with the dates in Column C. Visually, they're 2/1/21, 2/10/21, etc, which is appropriate for February. However, internally the cell shows a different date. For instance, C13 shows "02/01/2021 1:36 AM", but the internal date is 1/2/2021 1:36:00 AM, a value of 44198.07. The internal date needs to be "02/01/2021 1:36 AM", a value of 44228.07.
I thought the post would help with that, but I can't get it to work. The only solution I've been able to find is the formula =--IF(N(C13),TEXT(C13,"d/m/yy h:m"),C13)
Can Power Query do this, or is there a better way? I appreciate any help I can get.
Hi Jomili,
Can you please upload a sample file from your vendor so we can see how the dates are stored? Note: it's important the file is original, and hasn't been opened in Excel on your PC (assuming it's a csv or text file).
Thanks,
Mynda
I'm so sorry! I DID add an attachment with my original post, not sure where it went. Trying it again.
BTW, it didn't come as a CSV or text file, just a vanilla Excel file.
Still desiring some help.
Hi Jomili,
Sorry for the delayed reply and thanks for sharing your file. Whoever opened the file in Excel originally has messed up the dates. This data must have originally been in a different format (CSV/Text etc.) and then opened in Excel. The source file had a different date format (mm/dd/yyyy) to the version of Excel on the person's PC who opened the file in Excel (dd/mm/yyyy), and as a result unbeknown to them the dates have been messed up. You can see this when you look at the filters for column C (see screenshot attached), where dates with a day of 13 onward have been imported as text. You can tell they're text because they haven't been included in the month groupings in the filter drop down list.
If you cannot get the original source file that you can import using Power Query, then you'll need to fix the dates manually. You can see that your formula has fixed dates on days 1 through 12, and for dates 13 through 31st your formula returns an error, so you could change your formula to this:
=IFERROR(TEXT(DATE(RIGHT(TEXT(C13,"dd/mm/yyyy"),4),LEFT(TEXT(C13,"dd/mm/yyyy"),2),MID(TEXT(C13,"dd/mm/yyyy"),4,2))+C13-INT(C13),"mm/dd/yyyy hh:mm AM/PM"),C13)
Then copy and paste the formula as values, then convert the values to numbers/dates using Text to Columns on the Data tab of the ribbon.
Hope that points you in the right direction.
Mynda
Mynda,
Thanks for the alternative formula, but not sure it's needed. You said "and for dates 13 through 31st your formula returns an error" but it doesn't in my version of Excel, it works just fine. Maybe a difference in our versions?
Glad to know I wasn't misapplying Power Query, but actually had a different scenario for which PQ had no solution.
ok, if you say so. I can't test at my end because I have different date settings to you, hence my alternate formula 🙂
Hello,
As already mentioned by Mynda, the dates in the file is messed up, from row 2710 to the end of list the date/time value is text, in rows above it is numbers but altered with so instead of 1st of February you have 2nd of January as start date. The reason till why the dates are text from row 2710 is because Excel know that there exists no 13th month so it keeps the data as text instead.
This is a classsical import error, so if you received this Excel file from someone it means that the sending part have imported this data from somewhere, probably from a .csv file.
So either you hold on to your formula solution or you revert back to sender and ask them to do a correct data import next time.
Br,
Anders
