type mismatch converting string to date

Question

Issue found: date language is in russian. Yet next question is - how can I convert the date string on go depending on dateformat of specific user?

probably simple question. Smashing my head against the wall.

I have date in txt file and its being read as 01-Sep-21. Any action in VBA to work with it as date result in type mismatch - Datevalue, CDate, CLng - nothing is working. Where am I missing the issue?

test on raw input looks like this, it's not my code related issue (type mismatch on CDate):

upd: 01.09.2021 - works 01-09-2021 - works 09 replaced with Sep/September - doesnt work

CDate worked for me with the date you provided. Perhaps you can post your code so we can help you better? — Brian M Stafford
– Brian M Stafford, Commented Jul 15, 2021 at 16:38
@Rosetta great, you're correct. But my laptop is in English, but it looks like dateformat is in Russian as "01-Сен-21" works. How can I work around it for code, as it's being used by multiple people. Can you convert on go depending on language? — Andrey Karasev
– Andrey Karasev, Commented Jul 15, 2021 at 17:02
Looking at your revised image, if you are dimming DateProper as Date, you do not need to call CDate on it... just set DateProper = strDate — GoodJuJu
– GoodJuJu, Commented Jul 15, 2021 at 17:02
You can format Date/Time for a specific language and country in VBA: codekabinett.com/… explains this in good detail. — GoodJuJu
– GoodJuJu, Commented Jul 15, 2021 at 17:09

GSerg · Accepted Answer · 2021-07-15 18:07:44Z

3

CDate uses the current regional settings of the computer. If you need to parse dates in different locales, do so manually:

#If VBA7 Then
Private Declare PtrSafe Function VarDateFromStr Lib "OleAut32" (ByVal strIn As LongPtr, ByVal lcid As Long, ByVal dwFlags As Long, ByRef pdateOut As Date) As Long
#Else
Private Declare Function VarDateFromStr Lib "OleAut32" (ByVal strIn As Long, ByVal lcid As Long, ByVal dwFlags As Long, ByRef pdateOut As Date) As Long
#End If

Const LANG_EN_US as Long = &H409&
Const LANG_RU_RU As Long = &H419&

Dim s As String
Dim d As Date

s = "21-Sep-2021"
VarDateFromStr StrPtr(s), LANG_EN_US, 0, d
Debug.Print d
  
s = "21-" & ChrW$(1057) & ChrW$(1077) & ChrW$(1085) & "-2021"  ' 21-Сен-2021
VarDateFromStr StrPtr(s), LANG_RU_RU, 0, d
Debug.Print d

edited Jul 15, 2021 at 18:07

answered Jul 15, 2021 at 17:55

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

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

Comments

GoodJuJu · Accepted Answer · 2021-07-15 17:00:22Z

-1

You can easily change the date string to an actual date in VBA:

For example, in the image below you see that strDate is a String and datDate is a Date:

edited Jul 15, 2021 at 17:00

answered Jul 15, 2021 at 16:43

GoodJuJu

1,5912 gold badges22 silver badges40 bronze badges

Collectives™ on Stack Overflow

type mismatch converting string to date

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related