Textboxes date format problem

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
Dear MrExcel community,

I have a program for search & update. There are 32 column and almost 5 column are date. This dates writing on excel as "dd.mm.yyyy". But when I search a data dates comes to textboxs as "mm/dd/yyyy". But sometimes I don't know how, dates comes true format on textboxes. I need to dates must comes true format to textboxes all the time.

Thank you for your time and interest.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,171
Office Version
  1. 2016
Platform
  1. Windows
VBA somehow will convert internally into the US format of mm/dd/yyyy. So, it is a pain to those not using the US format :giggle:

However, internally it stored as date value, doesn't care about the display format. Just remember to reformat it when wanting to display back.
 

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
VBA somehow will convert internally into the US format of mm/dd/yyyy. So, it is a pain to those not using the US format :giggle:

However, internally it stored as date value, doesn't care about the display format. Just remember to reformat it when wanting to display back.
It’s not about only display format. When I update other datas, also date updated as wrong format. It is the problem :(
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,171
Office Version
  1. 2016
Platform
  1. Windows
I believe the problem is the program read the data from TextBox as text. It has no format as the pogram see it. The program has to be told how to read the data. If there are two different format mixed together, then it would be a challenge.
 

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I believe the problem is the program read the data from TextBox as text. It has no format as the pogram see it. The program has to be told how to read the data. If there are two different format mixed together, then it would be a challenge.
I don’t think so. Because on excel date format is “dd.mm.yyyy” but program read and convert as “mm/dd/yyyy”. If program read data as text it must not convert anything. I think there must be solution
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,171
Office Version
  1. 2016
Platform
  1. Windows
I don’t think so. Because on excel date format is “dd.mm.yyyy” but program read and convert as “mm/dd/yyyy”. If program read data as text it must not convert anything. I think there must be solution
It is like this:
Your Worksheet default format is "dd/mm/yyyy"

Say you have TextBox in UserForm. You enter date in same format as your Excel like "1/3/2021" (today date).

You want to write back to Sheet

VBA Code:
Private Sub CommandButton1_Click()
A = CDate(Me.TextBox1)
Range("C3") = Format(A, "dd/mm/yyyy")
End Sub

VBA will read "1" as month, ""3" as day and "2021" as year. So, what you see in Range("C3") would be 3/1/2021 to comply with your worksheet formatting.
 

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
It is like this:
Your Worksheet default format is "dd/mm/yyyy"

Say you have TextBox in UserForm. You enter date in same format as your Excel like "1/3/2021" (today date).

You want to write back to Sheet

VBA Code:
Private Sub CommandButton1_Click()
A = CDate(Me.TextBox1)
Range("C3") = Format(A, "dd/mm/yyyy")
End Sub

VBA will read "1" as month, ""3" as day and "2021" as year. So, what you see in Range("C3") would be 3/1/2021 to comply with your worksheet formatting.
Exactly but Im not working with todays date. This codes writes textbox todays date
 

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
Your Worksheet default format is "dd/mm/yyyy"

Say you have TextBox in UserForm. You enter date in same format as your Excel like "1/3/2021" (today date).

You want to write back to Sheet

VBA Code:
Private Sub CommandButton1_Click()
A = CDate(Me.TextBox1)
Range("C3") = Format(A, "dd/mm/yyyy")
End Sub

VBA will read "1" as month, ""3" as day and "2021" as year. So, what you see in Range("C3") would be 3/1/2021 to comply with your worksheet formatting.
I solved. That was easy.
VBA Code:
Private Sub txtT_Change()
txtT.Text = Format(txtT, “dd.mm.yyyy”)
End Sub
Thank you for your interest!
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,843
Messages
5,638,677
Members
417,041
Latest member
Molo

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
Top