The dang mm/dd/yyyy format...

[Assamita]

Hi. I've got an excel with a list of records, among the ones, there is a column with dates. I need them to be in format dd/mm/yyyy, and visually they are, but somehow internally they are in format mm/dd/yyyy because when I apply a filter to them, it displays values that don't exists: from first to fifth of July to December. I only have dates until the previous month, so it's interpreting the 12th of May as the 5th of December. That's why months from July to December 2018 only have values of the 1st to the 5th.
Pretty messy explanation, sorry about that.

These records are automatically coming from an Access file through a copyfromrecordset function, but I believe the error is at the excel file format. If I change the format, it changes the visual appearance of the field values but that's not what I want. I want it to properly interpret the date as it's displayed.

Thank you

 

[Assamita]

I've checked and the Access file has a date as 12/05/2018 but then the copyfromrecordset copies it to the excel as 05/12/2018. Do you know why is it doing that and how can I prevent it?
Thank you

 

[Johnny C]

it's a known 'feature' of the transfer between Access and Excel.

Are you using VBA to retrieve it? if so there's a few workarounds.

whichever column the date is in (I assumed B here) try

ActiveSheet.Columns("B").NumberFormatLocal = "dd/mm/yyyy"

Try it before and after see if it works.

When I say try it before and after, try each separately and then both before and after - CopyFromRecordSet sometimes overwrites the format, it can trash the format of the whole sheet in some circumstances

If that doesn't work (I can't test it here) try copying to a temporary sheet and then copy & pasting using the .NumberFormatLocal.

 

[Assamita]

Thank you, but that didn't work. What did work is to copy the values to a different sheet, so what I've ended up doing is deleting the full sheet where it was being copied and create it again from scratch. In the end, it was only the data from the Access and a pivot table. It's like the sheet was somehow corrupted and creating a new one seems to have fixed the issue.
Regards

 

[Johnny C]

Well at least you fixed it. CopyFromRecordSet is unpredictable, so you may find it happens again for no apparent reason.