Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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

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 ...
Display Numbers, Dates, & Times in the Correct Format in Excel
In this tutorial I am going to look at Number formatting. Number formatting allows you to change how numbers are di ...
Quickly Enter the System Date in Excel - Keyboard Shortcut
You can insert the system Date by holding the Control (Ctrl) key and pressing the colon or semicolon key. (Ctrl+;) ...
NOW() - Display The Current Time in Excel
This Excel tip will show you how to display the current time within any Excel spreadsheet. This is a useful functio ...

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

Good Monring Folk

I have a userform that picks up a date, in UK format e.g 11/12/2013, from a cell when it opens using this code, this is how it should be

Please Login or Register  to view this content.

When the user puts additional information into the form and saves it the date is being changed to the American format 12/11/2013 in the new cell, the code for this part of the operation is

Please Login or Register  to view this content.

full code below, I can't understand why the format is being changed. Can someone please point out if and where I went wrong and why its changing the format as I need it to remain in the UK format

Please Login or Register  to view this content.

Many thanks for your help

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

I have a similar problem to this thread

the basic idea is to set textbox1 in {my_}userform to 'now()' in the date format dd/mmm/yyy.
This is to speed up to filling in the user form. There is a calendar command button next to TextBox1 bringing up a separate calendar userform incase the user requires a different date, & I've successfully managed to get that to update TextBox1 no bother...

I CANNOT however get the calendar nor textbox1 to programaticaly show todays date!! {now()?}

HELP I'm stuck in a rut with this one!!
I need it to be able to copy similar initialises to other projects.

Here's what I've tried......

Option Explicit

Private Sub my_form_Initialize()
'TextBox1.text = Format(Date, "dd/mmm/yyyy")

'TextBox1.Value = Application.WorksheetFunction.Date(.Format, "dd/mmm/yyyy")

'TextBox1.Value = Date
'Date.Value = Format( Now() , "dd/mmm/yyyy")

'TextBox1.Value = Format(Date, "dd/mmm/yyyy")

end sub

all of the options that I have tried in various combinations & orders are 'commented out, NONE of them seem to work

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 am new to this forum today and i have been browsing few some old posts and found some quite userful information.

I am studying ICT at the moment and am making a project that when the user opens the excel file a userform pops up, is there a code or anything where i can just have the user form pop up? Not the back sheets in the background or anything but just the user form to open?

Also is there another such a code that stops the user from moving the userform around the screen?

If you could help me i would really appreciate it



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?


I have a userform asking the user for a date.
The info on the form then populates a spread sheet. However the date goes into the userform as text. e.g. in the form i enter 11/10/75. This populates the spreadsheet but it is populated exactly as 11/10/75 i.e. not a date, I have to actually click in the cell and press return for it to change to and be recognised as a date.

I can not figure out he code for this.

Any ideas

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 All.

The saga of my userform continues.

User is "happy"-ish with userform.. "except could you add this " and that and this..

but one issue which is a problem is the size of the userform. On my PC, both home and work, userform is fine.. on her's its huge... probably one third size bigger.. and because you can't scroll down the worksheet when the userform is open she can't access the lower section of the form.

So.. two questions, i guess.

1. can i change the settings within the VBA to re-size the userform to the user settings ( or something to allow her to see the form )

2. can the settings be changed to allow the scroll bar down the right side of the worksheet to be activated... ie so she can scroll down to see the lower sections.

thanks in advance.. and for all your help with this userform. Its been a lifesaver. Chuf

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


I am developing an excel app and I have three forms, new customer form a product development form and a shipping form. Each form uses (or will use) a calendar form so that the date is in correct format. I show the calendar form with a textbox_enter sub. How can I pass the name of the first form so the code in the calendar form puts the date value in the right forms date textbox?