Date Formatting

ChrisM92

New Member
Joined
Nov 4, 2020
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I've created a User Form for our compliance team to log their deductions in to try and reduce errors. However I'm having an issue with the date.

I have the below for when the form opens:

Private Sub UserForm_Initialize()
Dim xRg As Range
DateTextBox.Value = Format(Date, "dd/mm/yyyy")
DateTextBox.Locked = True

And then later on when they hit submit, the below moves it from the form onto the backing sheet:

Worksheets("Data").Range("B" & NextRow) = DateTextBox.Value

Somewhere along the way Excel is deciding that the date is American and so 04/11/2020 on the Form, becomes 11th April in the data sheet.

Any idea how to stop this?
 

Attachments

  • Input Form.PNG
    Input Form.PNG
    7.9 KB · Views: 1

Some videos you may like

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.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
Use DateValue to convert the value to a 'real' date value, and if required format the cell the value is going in to show the correct date format.
VBA Code:
Worksheets("Data").Range("B" & NextRow) = DateValue(DateTextBox.Value)
 
Solution

ChrisM92

New Member
Joined
Nov 4, 2020
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
Use DateValue to convert the value to a 'real' date value, and if required format the cell the value is going in to show the correct date format.
VBA Code:
Worksheets("Data").Range("B" & NextRow) = DateValue(DateTextBox.Value)

That was embarrassingly easy..

Thank you, that's worked!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,594
Messages
5,625,693
Members
416,127
Latest member
MALEPINZON

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