Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Date Format -usa To Uk In Userform

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Is there anyway of getting a userform to send the date out in UK format rather than American?

I have read many posts on it this afternoon and cant get any to work for my user form.

The database which the user form sends to cannot be changed.

Regards BVG

View Answers     

Similar Excel Tutorials

Get the Current Date in Excel
How to get the current date using Excel.  This method updates the date every day so that it is always accurate. To ...
Dates in Excel Explained
I'll explain how dates in Excel work and then point you to many useful tutorials on how to use dates and also time ...
Join a Date and Time into a Single Cell using VBA Macros in Excel
In order to combine a cell that has a date with a cell that has a time, using a Macro and VBA in Excel, you must su ...
Get the Last Day of the Month in Excel
How to get the last day of the month, including the date and day of week, for any date in Excel.  This method allow ...

Helpful Excel Macros

Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Format Cells as a Scientific Number in Excel Number Formatting
- This free Excel macro formats selected cells in the Scientific number format in Excel. This means the cell will be put
Format Cells as a Currency in Excel Number Formatting
- This free Excel macro allows you to quickly and easily format a selection of cells in the Currency number format in Exce

Similar Topics

hi evyone,
i urgently need some help!
I am having a problem with the date a on vba user form. I have searched high and low, and have come accross many threads but cant seem to fimd a solution.

Basicaly my user form reads and writes to a cell, but keeps changing the date format to us format.

please see attached file.

thanks in advance


I have a TextBox in a UserForm in which the user puts a date in any format of his choice. The routine checks that it is a recognisable date and then proceeds. In some cases I want to convert this date, in the TextBox, to the date of the last day in the month. I can do that. I can either specify the month-end date format to be used (e.g. =Format (dMonthEndDate, "dd-mmm-yyyy"), or, if I do not, I automatically get an American date ("mm-dd-yyyy") which is useless here in England.

My question is this: Is it possible, please, to pick up the date format in which the original date was entered by the user so that I can return the month-end date in the same format?


So I now have a nice little user form which will cut down data entry time by vast amounts....

Except the box for date is returning the data in the wrong format...

If I enter the date on the userform as 4/2/11
It is entered into the appropriate cell as 2/4/11 which is going to make analysis a bit difficult!

I have formatted the cells to UK date convention (dd/mm/yy) but it doesn't seem to make any difference...

The data box on the userform is a stock text box btw, not sure if this has any bearing on the situation?

Any advice much appreciated...


I have a problem showing the date in a user form in UK format. It displays in American format, does anybody know how to change this?

I've tried te below where M66 is the date in UK format but it still shows incorrectly.


TextBox8 = Range("M66")


TextBox8 = Date

Many thanks

Hi guy's,

I'm running into some trouble with my userform.

I have one sheet with dates in format dd/mm/yy (european)
These dates are used as input for a combobox in the userform.
The combobox shows the dates as they should be, no problems here.

After a date is selected, the value of the combobox is placed in a cell on my primairy sheet.
The format then is changed to the American style (mm/dd/yy). However the properties of the cell is still European. the date has changed.

For instance. 3-7-2011 = (#40609 =ok) is changed to 7-3-2011 = (#40727 =not ok)

Important for me is that the cell format must be a date, the correct date. Because formulas depend on this value, as a date.

Is there maybe a VBA code that copies the combobox value as a dd/mm/yy format?

Thanks a million


I have User Form with one textBox which is used for entering date. Here in India standard date format is DD/MM/YYYY. After data entry is completed through this User FOrm I have to pick up some entries based on date filed. However it seems that date format in User Form is MM/DD/YYYY by default. Thus when user enters 31/3/2006 ( user intend to enter date 31st March, 2006) the same is not recognised as date field in Excel WorkSheet. Similarly when user enters 1/4/2006 (user intend to enter date 1st April, 2006), the same is considered as 4th January, 2006.

Any solution to this problem?

Please help

This is my first attempt at putting together a form. I've got an idea of what I want it to do, but I am not at all experienced with the VBA code to make it work.

Here is my user form:

I want each of the entries to go to the column of the date that corresponds with the entries.

1. The Date button will prompt a calendar for the user to select from. I think my form needs an additional text box for the date to reside in until all of the fields are filled in.

2. The value entered into each form box should then enter into the column that coincides with the date entered. What would be the VBA commands to make this happen and where should they be placed at?

3. The OK button should send all the information to their individual cells and automatically take the user to a chart in Excel that shows the data and trends that I am trying to represent. The userform shoul also close.

4. The Cancel button should take the user to the excel worksheet where they can manually manipulte the data if so desired, and close this userform.

5. The Help button will prompt a dialog box with instructions on operating the spreadsheet.

The processes don't seem too complicated as all of the calculations and analysis will occur in the worksheet. However, I am just getting my introduction into VBA, so I really don't know what goes where.

Thanks in advance.

Hi All,
I have created an VBA userform in excel which allows a user to enter data via comboboxes and textboxes etc, some of which are then passed on to several listboxes within the form. At the end of the day, when the form is complete, I want to be able to "submit" or "save" the userform as a stand alone document. Preferably in a pdf format, but it could be html, or a word doc - a format that would allow me to view the document without having to use excel. I would also like the option of being able to email the userform and have tried using an example of some code posted on the forum, however it didn't work. Any thoughts on this?


For reasons that are a bit longwinded to explain, I have to have the date entered in a textbox on a userform in US format. Because the Form will be used in the UK this way of entering the date is somewhat alien. How can I check that the user has input the date in the correct format, MM/DD/YY? All I want to do is open a Message box if it has been entered as DD/MM/YY.

Nice easy one for you all out there

I have a userform with several dropdown boxes on it. The Dropdowns are used to allow the user to input the time. The spreadsheet they read from is in the format "hh:mm". When the userform 'reads' them they are displayed in the form 2.3. How dod I format the dropdown boxes to show the time in 24 hr clock mode??

I have tried:

UserForm("frmDialog1").dropdown32.Format = ("hh:mm")

but get an error.

Any help appreciated.


Hello Friends

I have one user form with Two Text box with spinbutton on each

i hv entered code:
#me.TextBox1.value = Date #
and same for the TextBox2 and both are formated to date by Code:
#me.TextBox1.value=Format(TextBox1.Value, "dd-mm-yy")# ' this code is entered under userform_activate
my system's short date is also set on dd-mm yy format (united kingdom style)
the SpinButtons SP1 & SP2 which is Comanded for each text box as per below code:

#Private Sub SP1_SpinDown()
Dim dt as Date
dt = Date 'I mean Current Date
dt = Format(dt, "dd-mm-yy")
If DateDiff("y", dt, me.TextBox1.Value) = 0 Then
MsgBox " This Date is Current Date, You cannot add future's Date"
me.TextBox1.Value = Datevalue(me.TextBox1.Value) + 1
End If
End Sub

Private Sub SP1_SpinUp()
me.TextBox1.Value = Datevalue(me.TextBox1.Value) - 1
End Sub

Private Sub SP2_SpinDown()
dt = Date
If DateDiff("y", dt, me.TextBox2.Value) = 0 Then
MsgBox " This Date is Current Date, You cannot add future's Date"
me.TextBox2.Text = Datevalue(me.TextBox2.Value) + 1
End If
End Sub
Private Sub SP2_SpinUp()
If DateDiff("y", STAdd.Sdate.Value, STAdd.InvDt.Value) = 0 Then
MsgBox "Transaction Date is greater then Invoice date"
me.TextBox2.Text = Datevalue(me.TextBox2.Value) - 1
End If
End Sub #

by above code i want textbox2's date should be similar or greater then the date of textbox two. in my previous verson of this userform i hv not added DateDiff cheking code, after i add DateDiff code my user form shows dates in "mm-dd-yy" format. i am facing this probel only once while i start the userform first time when i unload it n restart it the format automatically changes to dd-mm-yy

i request you to help me please with my codes, i donn mind in changing entire code or way

thanks in advance

Samir Shah


Having an issue with the date in a UserForm Txt field, displaying US Date format.
For this example, I use these test dates: a) 2 Mar 2013 and b) 13 Mar 2013

Column8: LDATE
Format (UK date format): d/mm/yyyy

UserForm (purpose:to edit records on the ws)
Field: txtLDATE
Poulated from LDATE in ws.

The date is pulled from the ws to the userform with this code:

Please Login or Register  to view this content.

If I don't have the 'with' section of the code, the date comes into the UserForm in US date format, as so:
a) 3/2/2013
b) 3/13/2013

and I thought I had resolved that with the addition of the 'with' section of the code - however , no - it's only a half-solution:

This is the problem:

If I have the 'with' section of the code, only dates with a day from 13th to the 31st format as UK date.
Any date from the 1st the the 12th day, format as US date. As so:
a) 3 Feb 2013 (wrong)
b) 13 Mar 2013 (right)

If instead of the 'with' section of code, I have EITHER of the following lines - the result is just the same:

Please Login or Register  to view this content.


Please Login or Register  to view this content.

And it makes no difference what the dateFormat is on the ws - whether it's the exact same format
as in the code for the UserForm or not.

If anyone could help me reslove this, i would be most grateful! I have searched for solutions but can't seem to find anything.


Hi all,

I created a user form and have a text box that should contain the date. When the user opens the form I'd like this cell to display as "mm/dd/yyyy". Indicating to the user that the date should be entered in that format.

Any ideas how I can accomplish this?

I have a similar request for a time field but If I can get the date, im sure i can figure out the time.

Any help would be great.

Thanks in advance.

I have a userform which on completion sends the relevant data to the relevant cells in excel. However I also need to send the form inputs to a person via email, in a readable format, and have no idea how to do this.

Do I need to set the layout in a word document, or can this be done in excel?

Any help would be appreciated, as would a pointing in the right direction, or to any helpful links.



I have a user form that enters data into my spreadsheet, which has been working well. However in the date field, if the user enters the date in a MM-DD-YY or similar format that enters the date fine on my worksheet, but if the user enters the date in a MMDDYY format I get a crazy date like 1/9/1924.

Is there a way to put it in the code that the date has to be entered in a certain format?

Thanks in advance for any help.


I have a userform with 2 pages. On the second page, 2 calendar controls should appear (start date and end date). When the user goes to the 2nd page of the userform, the calendars do not appear ON the userform, but separately from it, in the upper left corner of the sheet. When selecting a date, they also appear on the form with the date selected.

I dont't know why this happens. If I create a single page userform with the 2 calendars, they appear correctly, i.e. on the form. Can anyone help me?

Thanks, Rick

I have created a userform and now trying to ensure that users enter the correct information in each cell. There is a date field which needs to be in the format MM/YYYY. I have tried to use the below code but this seams to round up the year e.g. Jul 2009 becomes 07/2010.


Private Sub FromDate_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'FromDate being the text box on the user form where the  date is entered.
    If Not IsDate(FromDate) Then
         MsgBox "Input must be a date in the  format: 'mm/yyyy'"
        Cancel = True
        FromDate = Format(FromDate, "mm/yyyy")
    End If
End Sub

Can you please help me on this.


On my user form I have a text box for the date. When I look at the data some of them have been converted into american format mm/dd/yyyy instead of dd/mm/yyyy. It only appears on some. I have a bit of code making sure that the date is filled in but this shouldn't have an effect surely?


Private Sub txtstart_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Not IsDate(txtstart) Then
         MsgBox "Input must be a date in the  format: 'dd/mm/yyyy'"
        Cancel = True
        txtFrom = Format(txtFrom, "dd/mm/yyyy")
    End If
End Sub



Good Morning, Afternoon or Evening

I'm hoping you guys maybe able to help with a small problem i'm having with Excel

I have a simple form that is used to collect data from Senior Management im using Tools from the control toolbox and have created a Form on a sheet in excel this is mirrored onto another sheet so that a user can copy and paste this into a larger database

This exercise is being done every 3months or quarterly so once the info has been input onto the main database we can get a blank form and reverse the process and get information back onto the form so that the user will only need to update..

The thing is everything is working perfectly and the process seems perfect except that when I re copy data back into the form the date format has gone and has been replaced by the excel date format i.e. 15/08/09 becomes 40040

I know this may seem like a long winded way of doing things but my limitations on excel and using data that was already here this was the best solution given the timescales..

Any help would be most appreciated

Hi all,
I have writen this code to change the format of a date entered into a user form to Australian date format (i.e.dd/mm/yy).
My problem is that when the user enters the date as"ddmmmyy" (e.g. 22apr09) excel throws me error 13 "type mismatch".

If I don't use the cdate and format operators it will happily put in the ddmmmyy date, however if the date is entered dd/mm/yy (e.g. 1/2/09) excel changes the date into american format (ie mm/dd/yy) which changes the date (e.g. from 1/2/09 {1st of Feb 2009} to 1/2/09 {2nd of Jan 2009}) and corrupts the data in the sheet.


'set page check date and format
If Pg_chx_ListBox.Value "" Then
Pg_chx_ListBox.Value = CDate(Pg_chx_ListBox.Value)
Pg_chx_ListBox.Value = Format(Pg_chx_ListBox.Value, "ddmmmyy")
End If
Cells(ActiveCell.Row, 6).Value = Pg_chx_ListBox.Value

Anyone have any ideas as to what I can do to prevent this error even if the date is entered as ddmmmyy and force the date into australian date format?
Thanks very much in advance.

I have a userform that is activated by a command button on a worksheet. I want the first field in the form (RevDate) populated with todays date. I have the following code that works the first time the form is opened.

Private Sub UserForm_Initialize()
RevDate.Visible = True
RevDate = Format(Now, "dd/mm/yyyy")

End Sub

but at the end of the form there is an 'add form' do I get the new blank form to have todays date in the first field on the form.


I have a UserForm in which the User will be filling out a form. I need to understand how on the date TextBox I can have a calendar pop up and populate the TextBox when a date is selected.

I have seen there is an calendar in Additional Controls and have created a small form with the Calendar on it. On my main userform I have now told the the DateTextBox on Before Update to pop up the Calendar (this all works)

My problem is now.

What do I need to write as code on the Calendar form for it to populate my other UserForm once a date is selected. How can I do this?


I have set up a user form and I want to change the text box to a date format instead. This is in both the box and when the information is copied to a cell.

Also if a user enters the date in the wrong format a msg box should appear with the correct date format required to be entered shown.

Cheers BJ5352

Hi all,
I'm currently using a user form with current "UserForm_Activate" settings as follows:


Private Sub UserForm_Activate()
txtDate.Text = Format(Now(), "dd/mm/yyyy")

Up to this point I'm ok, as the date on the user form defaults to dd/mm/yyyy (e.g. 28/02/2011).

I also have the below code for when the user clicks on the "Enter" button on the user form, which transfers the date from the form to the spreadsheet:


Sheets("PrimeCo Home").Select
Selection.Value = txtGMT.Value
Selection.Offset(1, 0).Value = txtDate.Value
Selection.Offset(2, 0).Value = txtClientName.Value

The date does get transfered to the spreadsheet, but in the mm/dd/yyyy format (e.g. 02/28/2011).

I've changed the format under "Format Cells", both under the "Date" and "Custom" categories... with no luck.

I have even added code which should change the format to dd/mm/yyyy:


Selection.NumberFormat = "dd/mm/yyyy"

My computer's settings are also set up with the dd/mm/yyy format.

Can any one give me other suggestions?

Thanks in advance.

Hi. I'm stumped by a line of code that doesn't behave as expected.

I'm attempting to fill a userform label with a date taken from a cell in a worksheet when the user opens the userform. I need the date to be in "dd mmm yy" format, i.e. "26 May 08".

The label is populated with the correct date but in the wrong format - "26/05/2008".

The code reads:

frmPlacement.laPlaceDateStart = Format(.Cells(iRow, .[plcdatestart].Column), "dd mmm yy")

The line sits inside a "With...End With" for the worksheet object that the date is taken from.
frmPlacement.laPlaceDateStart is the userform label.
iRow is a variable for the sheet row; .[plcdatestart] is a named range.

Can anybody offer a reason why the Format does not work, and maybe a remedy?

Thanks in anticipation,