Excel (VBA): data formatted as date not recognized

Question

So I have an excel file, with some VBA code in it. The problematic bit is that I need to sort a lot of data by barcode number first, then by Date of visit (each barcode visits multiple times).

This all works fine with random test data that I generated. It gets all sorted by barcode, then by date from latest to earliest.Great!

But not, when I try to use actual data, it doesn't: the barcode gets sorted all right but not the date. The actual data will come from an Android app (Xscan) that has CSV files as output.

Unfortunately, the data exports looks like this "21/10/2014". When I try to have it sorted, it only sorts it according to the very first number, not as a date (not very useful)... so no matter what the month is, if the day is 31 st, it'll be place highest.

I have tried the following: changing datatype back & forth, and run the code with datatype set to different things, but it still doesn't work.

****How can I make Excel understand that 21/10/2014 is to be read as a date, even though it comes from a CSV file and looks like a string/weird division?****

Ideally, the solution could be coded into VBA somehow... I'm dealing with very low-tech skills users....

EDIT: See screenshot below: the column I need to sort by is column H. It doesn't matter if I set data type to Number, date, general.... Still does the same. As for the code:

Function sorting_all(mySheet As Worksheet, myRangeRow As Range, myRangeCol As Range, secondSort As String)
Dim myCol As Range
Set myCol = getCol(mySheet, secondSort)
mySheet.Range("A1", mySheet.Cells(myRangeRow.Row, myRangeCol.Column)).Sort key1:=mySheet.Range("A1"), order1:=xlAscending, key2:=mySheet.Range(Columns(myCol.Column).Address()), order2:=xlDescending, Header:=xlYes, Orientation:=xlSortColumns

End Function

The barcode is in column A and always will be, hence the hardcode (I know I know). That's the key1. Key2 is the date of visit (though the function is called to sort other things, hence the secondsort variabel).

enter image description here

Further EDIT: So apparently the output file from the application isn't really saved as a .CSV (even though that's what the app claims), it is saved as a Excel 97-2003 file. If I copy-past from that file, it doesn't work. If I saved the file as a CSV (properly), THEN copy-past from it.... it works. Not gonna lie, I hate Excel. Not built-in function to change this from the app. So the heart of the matter might be between the Excel 97-2003 file format & my Excel 2010 file that I use... In any case it does seem that importing the data through VBA will be the best answer.

upload more info like: your csv file or screenshot of it. What is your Date format for the cell in question? Upload if you have tried some code. — ZAT
– ZAT, Commented Oct 21, 2014 at 19:07

Richard Le Mesurier · Accepted Answer · 2014-10-22 05:34:56Z

1

I would add in some new columns and manually parse those dates if Excel will not recognise them.

To parse the dates, use formulas like:

'=left(H1, 2)' in I1 to get day
'=mid(H1, 3, 2)' in J1 to get month
'=right(H1, 4)' in K1 to get year
'=date(I1, J1, K1)' in L1

Copy, Paste Special...Values on column L, and delete columns H:K.

Adding in 4 columns of formulas (with your VBA script) is not going to slow things down much - this is exactly what Excel is made for. Even with thousands of rows.

edited Oct 22, 2014 at 5:34

answered Oct 21, 2014 at 19:46

Richard Le Mesurier

29.8k23 gold badges144 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

logicOnAbstractions Over a year ago

Hmmm yeah that would be one way to go. Though some remark: - At least now, Excel VBA DOES sort data presented as DD/MM/YYYY (my self-generated test data, with properly formatted data type, worked fine). I would rather have it read what is already formatted as a DD/MM/YYYY for what it is. Also, the problem is that there's actually a lot of code running on this for a couple thousand lines so it could get slow....

Ron Rosenfeld · Accepted Answer · 2014-10-21 19:51:05Z

0

In most similar cases, the problem is that the imported date does not match the settings in your Windows Control Panel Regional settings. So excel interprets dates that do NOT fit those settings as text, and probably converts the other dates incorrectly.

Several fixes can be used.

Alter the output from your data source so the date formats match -- usually not feasible.
Change your windows regional short date settings to match the csv format -- probably not a good idea with your "lo-tech users"
IMPORT the file rather than opening it. Whether you do this manually or via VBA, you will then have the opportunity to use the Text Import Wizard, which will allow you to specify the imported date format.

answered Oct 21, 2014 at 19:51

Ron Rosenfeld

62.1k7 gold badges35 silver badges71 bronze badges

3 Comments

logicOnAbstractions Over a year ago

I was just about to edit my post to say that: if I IMPORT the file it does work, because, as you said, you can use the Text Import Wizard.... So far my preferred option. But it still seems dumb to me: it really ought to be possible to decently have excel recognized those data for what they are. If I don't find something else that'll be the answer, but I think I'll give it a few days...

Ron Rosenfeld Over a year ago

@Francky_V That is probably the safest method to use, and do it via VBA so as to properly set the date format.

logicOnAbstractions Over a year ago

Importing through VBA it is then!

Collectives™ on Stack Overflow

Excel (VBA): data formatted as date not recognized

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related