1

I can't figure out how to tell VBA the correct date format, as it seems to assume in the wrong way around. So CDate("24/02/2016 10:59") is giving the correct date - dd/mm/yyy. However when it iterates through a date like CDate("01/03/2016 00:59") - it assumes number 03 is not a month but a day so VBA is assuming it's mm/dd/yyyy, so it's giving me a date 03/01/2016 which is wrong and messing my data. So how can I tell VBA it's actually dd/mm/yyyy format. VBA seems to automatically pick up nicely even if it's "2016/01/14", but it's failing when it's not really obvious which part of numbers are months e.g. both dd and mmare less than 12.

I'm reading this date from a CSV file using WS.Cells(irow, icol).Value.

This is what I tried so far:

datDate = CDate(Format(WS.Cells(iRow, iCell).Value, "dd-mmm-yyyy hh:mm:ss"))

Improve this question
1
  • 1
    I am almost certain that this has to do with your regional date settings (I assume you are outside of US, correct?). US standard date format is mm/dd/yyyy and VBA uses this format even if the regional settings in your Excel app are different. You'll most likely need to parse the date to ensure you get the correct month and day as @Gustav suggested below. Commented Apr 5, 2016 at 16:55

3 Answers 3

Reset to default
3

When the CDate() function encounters an ambiguous "aa/bb/yyyy" date string it will be interpreted according to the order that the Month and Day appear in the short date format defined by the Regional Settings in Windows.

For example:

When my machine is set to "English (United States)" with a short date format of M/d/yyyy then Month(CDate("02/04/2016")) returns 2.

However, after changing my Regional Settings to "English (Canada)" with a short date format of dd/MM/yyyy then Month(CDate("02/04/2016")) returns 4.

Note that this is different from the behaviour of the Jet/ACE database engine when interpreting ambiguous #aa/bb/yyyy# date literals in SQL statements. In that case it will always interpret them as mm/dd/yyyy regardless of the regional settings.

The best solution, as always, is to ensure that the string representation uses an UNambiguous date format, e.g., 2016/02/04 will always be interpreted as Feb 4.

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

Comments

2

Use mm/dd/yyyy hh:nn:ss like:

#03/01/2016 16:32:58#

or DateSerial and TimeSerial:

DateSerial(2016, 03, 01) + TimeSerial(16, 32, 58)

To parse the string, use Mid:

TrueDate = Dateserial(Mid(s, 7, 4), Mid(s, 4, 2), Mid(s, 1, 2)) + TimeSerial(Mid(s, 12, 2), Mid(s, 15, 2), 0)

s = "01/03/2016 00:59"
' Returns: 2016-03-01 00:59:00 

s = "24/02/2016 10:59"
' Returns: 2016-02-24 10:59:00
Improve this answer

6 Comments

I'm trying to read data not trying to change the existing data, which comes in every month in this date format.
That was not what you told.
Gustav sorry to be vague, I edited my question. Sorry your code is still producing 03/01/2016 00:59:00. I'm getting very anxious!
No, you are just confused. The code returns the correct values - see edited answer with results.
if you do stuff within VBA Access it works just fine as you mentioned, my guess is when you read from Excel csv file, it might store your s as 03/01/2016. I stopped testing further - I will just change date format to yyyy/mm/dd for piece of mind. Thanks anyway!
|
0

Change the format of the date before you convert. Something like this;

Dim strDate As String, strFormatDate As String
Dim dte As Date

strDate = "01/03/2016 00:59"

strFormatDate = Format(strDate, "dd-mmm-yyyy hh:nn")

dte = CDate(strFormatDate)

This changes the date string to 01-Mar-2016 00:59 which should convert to a date data type without confusion.

Improve this answer

3 Comments

Sorry this is not working either, it's still converting to "03/01/2016 00:59:00". If you test your code in a separate subroutine - it works fine, so is my previous code CDate("01/03/2016 00:59"). For the record, I'm reading this date from a CSV file using WS.Cells(irow, icol).Value. I'm very confused...
@Kudrat - try using .Value2 instead of .Value
@Scott, sorry no success

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.