Excel Frustration with Dates

Ralph Gregory

Board Regular
Joined
Aug 14, 2003
Messages
125
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,569
Office Version
  1. 2010
Platform
  1. Windows
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).
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,569
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,569
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,632
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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 :)).

1593966360826.png
 

Watch MrExcel Video

Forum statistics

Threads
1,118,607
Messages
5,573,193
Members
412,513
Latest member
PayneEdward
Top