September 16, 2016
I am using Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20186) 64-bit on a PC.
When I export a file of February transactions, I see two different date formats.
February 1st to February 12th are Custom format dd-mm-yy h:mm, one example looks like this in the cell:
and it looks like this is the formula bar: 02-12-2023 5:07:22 PM.
Because I pulled a February report, I know that transaction was February 12th. But according to the Custom format of dd-mm-yy, it would mean we received that contribution on December 2, 2023, and obviously that did not happen as it is nine months in the future.
February 13th to February 28th are General format and my example looks the same in the cell and the formula bar: 02/13/2023 01:11:40
I would like to format the cells so that all dates are formatted the same way, but I can't figure out a way to do this because currently, due to the custom format that has been applied to the first half of February, it thinks that 02-01-23 is January 2, 02-05-23 is May 2, 02-10-23 is October 2, and so on.
And then the General format that has been applied to the second half of February won't recognize these as dates so I'm unable to custom format those ones either.
I've attached my file, exactly how it is after exporting.
Thanks in advance for any advice you can give.
July 16, 2010
This is a common locale issue. i.e. you've opened the file in a different locale to the one it was created for. You can use Power Query to fix the dates using the 'Change type > using locale' tool.