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...
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,569
Office Version
  1. 2010
Platform
  1. Windows
What if you split the date into parts and create the date from those parts, does that work for you...
VBA Code:
Arr = Split( Worksheets("Invoice").Shapes("tbDate").TextFrame.Characters.Text, "/")
.Range("B" & a).Value = DateSerial(Arr(2), Arr(1), Arr(0))
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,719
Office Version
  1. 2013
Platform
  1. Windows
Try
VBA Code:
.Range("B" & a).Text = Format(Worksheets("Invoice").Shapes("tbDate").TextFrame.Characters.Text,"dd/mm/yyyy")
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,569
Office Version
  1. 2010
Platform
  1. Windows
Did you try the code idea I posted in Message #2 yet?
 

Ralph Gregory

Board Regular
Joined
Aug 14, 2003
Messages
125

ADVERTISEMENT

What if you split the date into parts and create the date from those parts, does that work for you...
VBA Code:
Arr = Split( Worksheets("Invoice").Shapes("tbDate").TextFrame.Characters.Text, "/")
.Range("B" & a).Value = DateSerial(Arr(2), Arr(1), Arr(0))
Hi Rick I had thought of doing that but it seems disappointingly clunky and shouldn't be necessary i Microsoft got their coding right in the first place.. :)
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,633
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@Ralph Gregory What does the below give you?


VBA Code:
With Worksheets("Invoices")
.Range("B" & a).Value = CDate(Worksheets("Invoice").Shapes("tbDate").TextFrame.Characters.Text)

End With
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,118,609
Messages
5,573,207
Members
412,514
Latest member
LangdonJohn
Top