VBA macro and File/Date question

thelad

Board Regular
Joined
Jan 28, 2011
Messages
237
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?
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,685
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 28, 2011
Messages
237
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,685
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
=TEXT(A1,"mm/dd/yyyy")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,800
Messages
5,626,958
Members
416,211
Latest member
lanka123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top