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

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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))
 
Upvote 0
Try
VBA Code:
.Range("B" & a).Text = Format(Worksheets("Invoice").Shapes("tbDate").TextFrame.Characters.Text,"dd/mm/yyyy")
 
Upvote 0
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.. :)
 
Upvote 0
@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:
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top