PDA

View Full Version : UK Date Format not US



craigos
04-08-2012, 01:14 AM
Hi All,

Home 2007....Work 2003

After much searching I need to ask for help on showing me exactly how to ensure a UK date format is showing on my sheet.

I have a UF with a Textbox where users enter a UK Date as dd/mm/yyyy.

Select Add button and date is sent to sheet but shows as US format - I have to have UK format only.

I have checked Regional settings, looked at many sites giving many many different ways of changing to UK, but alas nothing seems to work....probably my lack of experience!!, I am aware of US Centric for dates in VBA but can't work it out to show UK.

I have attached a much simplfied workbook than my full version in the hope that someone can provide a working example.

In Anticipation

Craig

Bob Phillips
04-08-2012, 03:39 AM
Cells(emptyrow, 1).Value = CDate(TextBox1.Value)

craigos
04-08-2012, 03:53 AM
xld,

Yet again...perfect.....a supplementary question if I may:

How can I ensure validation of the Date entry, so if it is not dd/mm/yyyy a message box shows to that effect.

I do have a mesage box for no entry and prevents user moving on / adding data to sheet, but as I use the UK date format for some Sumproduct calculations later just need to validate it.

Craig

Bob Phillips
04-08-2012, 05:28 AM
I don't think you need to worry about the format, Excel stores the date as a long regardless of the format, you just need to ensure that VBA doesn't screw it up, which CDate allows.

As such, I wouldn't worry if they input 31/05/2012, 30/5/2012, or even 30-May-2012, and I would just validate it for a date


Private Sub CommandButton1_Click()
Dim inputDate As Variant
Dim emptyrow As Long

With Worksheets("Sheet1")

On Error Resume Next
inputDate = CDate(TextBox1.Value)
On Error GoTo 0

If IsDate(inputDate) Then

emptyrow = WorksheetFunction.CountA(.Range("A:A")) + 3

.Cells(emptyrow, 1).Value = inputDate

Unload Me
Else

MsgBox "Invalid date"
End If
End With
End Sub

craigos
04-12-2012, 12:19 PM
xld,

Apologies for delay in replying....been away.

Thanks....again excellent response and my Boss is smiling, although I have alluded to getting help.

Thanks again

Craig:rotlaugh:

Bob Phillips
04-12-2012, 01:36 PM
Keep shtum mate, your boss will be happy to allocate all the blame to you when things go wrong :)