Excel Frustration with Dates

[Ralph Gregory]

Hi all I've racked my brains with this and I keep coming across the same problem
I have a text box ("tbDate") which I can type a date into in uk format "04/07/2020" ("dd/mm/yyyy") works fine and looks fine. When I save my data to another sheet using the following code
With Worksheets("Invoices")
.Range("B" & a).value = Worksheets("Invoice").Shapes("tbDate").TextFrame.Characters.Text
more code
end with
the result saves in US format "07/04/2020" even if i have the cells formatted as UK date
I even added the following code line to force it into uk Format
.Range("B" & a).value = Format(Worksheets("Invoice").Shapes("tbDate").TextFrame.Characters.Text,"dd/mm/yyyy")
and it made no difference!!
and yes my regional settings in windows 10 and excel are set to uk as is my default english and keyboard settings
Using 2019 version of excel ....Ive searched the web and it seems many people have the same problem... anyone got a solution?
If the day number is greater than 12 the dates tranfer Correctly...

 

[Rick Rothstein]

I am not sure if there is a problem or not, but you should check Mark's solution to see if it works correctly for dates where the day number is less than 13 and not the same as the month number (for example, March 11th of any year).

 

[MARK858]

not the same as the month number (for example, March 11th of any year).

Still get 11/03/2020 in my UK version (which is correct).

 

[Rick Rothstein]

As I said, I was not sure if there would be a problem for the OP or not, but string date conversions colliding with different month/day ordering are a recurring theme on the forums, so I wanted the OP to verify the results are what he expected. Just so you know, CDate("11/03/2020") gives me November 3rd as an answer here on my computer (set to US locale), not March 11th like it does for you. As long as the date string is being interpreted by VB within the locale the date string is created for, I guess it will work. The procedure I posted in Message #2 is guaranteed to work no matter the user's locale (as long as you know the intended ordering of the date string) as it avoids letting VB use its "evil type conversion" to make the decision as to what the date is supposed to be by creating a real date value (the underlying floating point number for the date) directly.

 

[MARK858]

but string date conversions colliding with different month/day ordering are a recurring theme on the forums

Don't I know it living in the UK, with UK Excel.

 

[Rick Rothstein]

The solution is simple, just get the UK to adopt the more logical American system of dates... problem solved.

 

[MARK858]

Bah.... or do what I done in my last firm and train our US counterparts to use ISO 8601 format when sending me data as most areas in the world either use DMY, YMD or both (and yes I did use it as well ).

 

[Rick Rothstein]

...use ISO 8601 format...

Yeah, that might be just a smidgen better than everyone adopting the US date system (it's close though?).

 

[JLGWhiz]

Hi Mark858 yes that works! i will have to look up cdate...

Use this link: Type Conversion Functions - Visual Basic