VBA macro and File/Date question

[thelad]

Hi,

I have a VBA macro working that compares one set of data against another. So basically the macro imports a source file that I save down.

The file we get appears to be Text (Tab Delimited).

When I compare my data against this files date columns a lot of dates are not matching with each other.

However if I first save the source file as a .xlsx file first and import this to compare the date fields will match.

I was thinking then of in stead of user saving as .xlsx I would get macro to open the source file and then resave it as .xlsx file and then import that file, however when I do it it doesn't match up the dates.

What is difference between me just saving file in .xlsx and the macro doing it automatically? first way works and date matches other way doesn't.

Strange? Any ideas of how to fix maybe?

 

[Joe4]

Are you using a European version of Excel?
The reason I ask is because it might be using a different date format (d/m/y instead of m/d/y).
The issue is that I think VBA always uses the US version, so the month/days on your dates could be getting reversed.

 

[thelad]

Hi,

Yes it would be European/Uk version. So do you think the best thing is to just save the source file as excel first then and work from there?

Its strange because it seems the source file has two different types of formats in the date column. Some dates are aligned to left in the cell while others look to be in the right of the cell

The ones that appear left aligned seem to be formatted to General when right click and check format. These are actually matching with my file.

The ones that appear right aligned seemed to be formatted as date. (With UK Date format)

So not sure why the general ones are matching my file.

My file downloads from system and it appears to have custom format mm/dd/yyyy

Not sure whats best to do? Maybe bes tto just save source file as excel.

Any thoughts?

 

[Joe4]

Unless someone explicitly changed the cell justification, if an entry is left-justified, it means that it is a text entry and not a date entry.
The fact that it is formatted as General confirms that, as all dates are really just numbers (the number of days since 1/0/1900) with a date format. If you change a valid date to the General format, it will show a long integer.

So it sounds like you have a mixture of dates and text in your data, which explains why you have the different behaviors.

If the the left-justified ones are matching your file, that means that your other file is text to.
You can convert a valid date entry to text using the Text function, i.e.

=TEXT(A1,"mm/dd/yyyy")