Excel confuses dd/mm when dd < 10

[terrafutan]

Hi all. I need some help with dates and Excel.

My short date regional format is dd/mm/yyyy.
I have a form with a textbox that initialises to yesterday and can be modified. 'TextBox64.Value = Format(Date - 1, "dd/mm/yyyy")'

When saving the form data to a sheet the following happens:
1. Where dd>12 it is captured correctly
2. Where dd<12 saves the dd as the month... it's driving me crazy.

For example today it populates 16/08/2023, I modify it four days ago, the 12/08/2023 and it's suddenly we're in December as the sheet is populated as 08/12/2023.

What am I missing?

 

[RoryA]

If you are using code to assign the textbox value to a cell, use CDate to coerce it to a date value using your regional settings.

 

[MARK858]

What am I missing?

Once you bring it into the text box it is just a text string of 12/08/2023 and all VBA is in American date format of MM/DD/YYYY by default.
The 16/08/2023 works when you put it in a cell (but will be text not a real date) because it can't be coerced to an American format date, but the 12/08/2023 will be coerced to a date in the format mm/dd/yyyy (then the underlying value will go into the cell and take on whatever format the cell has and so becomes 08/12/2023 [12th December 2023]).

As @RoryA has stated use something like

Range("A2").Value = CDate(TextBox64.text)

 

[terrafutan]

Thanks guys, that sorted the issue out.

'all VBA is in American date format of MM/DD/YYYY by default.' this I wasn't aware of, interesting.

Upticked Mark as solver due to extra info, thanks

 

[MARK858]

You're welcome, happy we could help