Date format in a userform

paliman

Active Member
Joined
Jul 7, 2002
Messages
254
Hi all.

I created a userform to input data in a spreadshit every day.

The first field is the date. By default, a textbox shows the current date. The code is

Private sub Userform_Activate
txtDate.Value = Format (Now, “dd/mm/yyyy”)
End sub


And that’s fine.

The problem is that when I copy the values of the userform to the spreadshit, the date changes it’s format to mm/dd/yyyy. So, When I open the userform I see 04/03/2004 for march 4th. But when that is copied to the worksheet, it becomes april 3rd, although it still looks “04/03/2004”.

The code I’m using is:

With ActiveCell
.value = txtDate.value

Any help will be greatly appreciated.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,827
Office Version
  1. 365
Platform
  1. Windows
The month and date and getting reversed because you are storing in a different order that Excel expects it to be in. This should fix that:
Code:
    With ActiveCell
        .Value = DateSerial(Right(txtDate, 4), Mid(txtDate, 4, 2), Left(txtDate, 2))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,871
Messages
5,766,857
Members
425,382
Latest member
IronM

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