0

I am placing a variable date value into a VBA text box (to prepopulate it), but excel is converting the date format from Australian (dd/mm/yyyy) to US (mm/dd/yyyy). Note this question is primarily about unwanted changes to the date format displayed in the text box, not the unwanted format coming out of the text box (though that is also obviously a problem!).

I have checked that the value immediately prior to it being placed in the text box is in the desired format; but it is then converted and displayed in US format. When I retrieve the value from the text box and place it in a spreadsheet it is in the unwanted US format.

Please note that Cdate does not appear to fix this. I used Cdate on the string coming out of the text box but it is still US format when I place the result into a spreadsheet. I have also tried using Cdate on the string going into the text box but it is still US format.

Any suggestions on what's going on would be much appreciated!

DavidWalker
  • 336
  • 1
  • 8
  • Please note that dates do not have a format. The Excel date for now is `44788.6741458333` at the time I wrote this. There is no format. When you format a cell that contains this value, though, it turns the number into a string ***which is formatted***. So, the question is are you dealing with text that is formatted or a Date value (which is not). In your question you say you have a "variable date string", which really doesn't make this clear. – Enigmativity Aug 15 '22 at 06:43

1 Answers1

0

To populate the textbox you could use Format Function, to force the date format displayed :

Textbox1.Text=Format(yourdate,"dd/mm/yyyy")

The default date format in your spreadsheet is linked to the regional settings of your computer but if you can specify what format is used by selecting it with Format Cells Format Cells window

However, i'm note sure how Excel can detect what date format is retrieved from the textbox. You might want to use a date picker instead of a textbox (Formatting MM/DD/YYYY dates in textbox in VBA)

Thomas C.
  • 217
  • 1
  • 12