VBA UserForm date format reversal dd/mm/yyyy into mm/dd/yyyy

[TheDeltaPete]

Hi,

I have been successfully using the following in a UserForm, which auto-generates the current date and time to transfer to a sheet:

txtDate.Value = Now
txtDate = Format(txtDate.Value, "dd/mm/yyyy")
txtTime.Value = Now
txtTime = Format(txtTime.Value, "hh:mm")

It has been correctly transferring as dd/mm/yyyy for many months, and for reasons unknown, the dd/mm has suddenly started appearing as mm/dd on the sheet instead?

Help?!?!

 

[6StringJazzer]

Welcome to MrExcel TheDeltaPete!

There is nothing in your code related to a sheet, just the text boxes. It would help to show all of your code.

If you are saving the contents of the text boxes to a sheet, then be aware that you are saving text, and Excel will do its best to convert it to a date/time value. How it does this depends on your localization. What are your Windows/Excel date format settings? If you are in the U.S., it will see 08/11/2023 and think it is Aug 11, when you intend for it to be Nov 8. Further, the way you see it in the worksheet depends on how you have the cells formatted. What format is applied to the cells where you are storing the data?

Also, the code above can be simplified. There is no need for the intermediate step.

txtDate = Format(Date, "dd/mm/yyyy")
txtTime = Format(Now, "hh:mm")

 

[TheDeltaPete]

Thanks for your reply. I omitted the full code as the problem was originating in the UserForm itself and displaying mm/dd there instead of dd/mm. The sheet was correctly set to date, and as mentioned, had been working fine for a long time befomre changing for no apprent reason.

There was something truly odd going on as I opened the code, changed nothing, saved, closed, reopened and it reverted to the correct dd/mm again. I saved, closed, reopened, and it went back to the error. Unfathomable.

However, I have taken your advice and simplified my code and all is working just fine, so I will see how it continues from now. Thank you!

 

[6StringJazzer]

the problem was originating in the UserForm itself and displaying mm/dd there instead of dd/mm

I'm struggling to understand your problem, because your first post doesn't say that.

Glad to hear you're making some progress. For this kind of problem it might help if you had a way to share a file.