Problems with date format in userforms
 

I'm having trouble with dates entered via a userform. When
I enter a date in the format dd/mm/yyyy everything is ok.
I then go and work out the work days between the 2 dates
entered. When I next enter the form it shows my date in
the format mm/dd/yyyy and if I don't notice and re-enter
the date I work out the wrong work days.

What do I need to do to get around this problem?

Data entered from a userform doesn't seem to take any
notice of cell format definitions. The cell is defined as
date of type 14/03/2001.

The user enters a start and end date then clicks a button
which has the associated code:

If Not IsDate(Me.TB_A1_SDt.Text) Then
MsgBox ("Must be a valid Date!")
Me.TB_A1_SDt.Text = ""
Me.TB_A1_SDt.SetFocus
Else
If Not IsDate(Me.TB_A1_ED.Text) Then
MsgBox ("Must be a valid Date!")
Me.TB_A1_ED.Text = ""
Me.TB_A1_ED.SetFocus
Else
Sheets("AutumnHT1").Cells(2, 1).Formula = CVDate
(UserForm1.TB_A1_SDt.Text)
Sheets("AutumnHT1").Cells(1, 4).Formula = CVDate
(UserForm1.TB_A1_ED.Text)
Unload UserForm1
Load UserForm2
UserForm2.Show
Exit Sub
End If
End If

End Sub

I needed to use the CVDATE function to ensure that it was
copied from the userform to the cell as a valid date
otherwise it was text.

Problems with date format in userforms
 

What are the 'Long' and 'Short' date formats in regional settings on your PC? Excel uses the regional settings by default and the default for Excel is US i.e. consistent with mm/dd/yyyy. You need to set them to dd/mm/yyyy

Look into: START | SETTINGS | CONTROL PANEL + REGIONAL OPTIONS on the 'Date' tab.

Problems with date format in userforms
 

They're both set to UK settings, i.e. 27/04/2004 for short
and 27 April 2004 for long. Is there somewhere else where
dates are set?


-----Original Message-----
What are the 'Long' and 'Short' date formats in regional
settings on your PC? Excel uses the regional settings by
default and the default for Excel is US i.e. consistent
with mm/dd/yyyy. You need to set them to dd/mm/yyyy.

Look into: START | SETTINGS | CONTROL PANEL + REGIONAL
OPTIONS on the 'Date' tab.
.

Problems with date format in userforms
 

In WORD, verify the language set for Office
Tools | Language | Set Languag

Make English (UK) the default.

Problems with date format in userforms
 

I am not sure which is the problem: the format when you enter a date, or how a date is Formatted in the textbox, so try this

Convert To your format in the textboxes Afterupdate event

Private Sub TB_A1_SDt_AfterUpdate(
If Not IsDate(Me.TB_A1_SDt.Text) The
MsgBox ("Must be a valid Date!"
Me.TB_A1_SDt.Text = ""
End i
Me.TB_A1_SDt.Text = Format(Me.TB_A1_SDt.Text, "dd/mm/yyyy")
End Su

----- Brian C wrote: ----

I'm having trouble with dates entered via a userform. When
I enter a date in the format dd/mm/yyyy everything is ok.
I then go and work out the work days between the 2 dates
entered. When I next enter the form it shows my date in
the format mm/dd/yyyy and if I don't notice and re-enter
the date I work out the wrong work days

What do I need to do to get around this problem

Data entered from a userform doesn't seem to take any
notice of cell format definitions. The cell is defined as
date of type 14/03/2001

The user enters a start and end date then clicks a button
which has the associated code

If Not IsDate(Me.TB_A1_SDt.Text) The
MsgBox ("Must be a valid Date!"
Me.TB_A1_SDt.Text = "
Me.TB_A1_SDt.SetFocu
Els
If Not IsDate(Me.TB_A1_ED.Text) The
MsgBox ("Must be a valid Date!"
Me.TB_A1_ED.Text = "
Me.TB_A1_ED.SetFocu
Els
Sheets("AutumnHT1").Cells(2, 1).Formula = CVDat
(UserForm1.TB_A1_SDt.Text
Sheets("AutumnHT1").Cells(1, 4).Formula = CVDat
(UserForm1.TB_A1_ED.Text
Unload UserForm
Load UserForm
UserForm2.Sho
Exit Su
End I
End I

End Su

I needed to use the CVDATE function to ensure that it was
copied from the userform to the cell as a valid date
otherwise it was text

Problems with date format in userforms
 

Private Sub TB_A1_SDt_AfterUpdate(
If Not IsDate(Me.TB_A1_SDt.Text) Or
Me.TB_A1_SDt.Text < "" The
MsgBox ("Must be a valid Date!"
Me.TB_A1_SDt.Text = ""
End i
Me.TB_A1_SDt.Text = Format(Me.TB_A1_SDt.Text, "dd/mm/yyyy")
End Su


----- chris wrote: ----

I am not sure which is the problem: the format when you enter a date, or how a date is Formatted in the textbox, so try this

Convert To your format in the textboxes Afterupdate event

Private Sub TB_A1_SDt_AfterUpdate(
If Not IsDate(Me.TB_A1_SDt.Text) The
MsgBox ("Must be a valid Date!"
Me.TB_A1_SDt.Text = ""
End i
Me.TB_A1_SDt.Text = Format(Me.TB_A1_SDt.Text, "dd/mm/yyyy")
End Su

----- Brian C wrote: ----

I'm having trouble with dates entered via a userform. When
I enter a date in the format dd/mm/yyyy everything is ok.
I then go and work out the work days between the 2 dates
entered. When I next enter the form it shows my date in
the format mm/dd/yyyy and if I don't notice and re-enter
the date I work out the wrong work days

What do I need to do to get around this problem

Data entered from a userform doesn't seem to take any
notice of cell format definitions. The cell is defined as
date of type 14/03/2001

The user enters a start and end date then clicks a button
which has the associated code

If Not IsDate(Me.TB_A1_SDt.Text) The
MsgBox ("Must be a valid Date!"
Me.TB_A1_SDt.Text = "
Me.TB_A1_SDt.SetFocu
Els
If Not IsDate(Me.TB_A1_ED.Text) The
MsgBox ("Must be a valid Date!"
Me.TB_A1_ED.Text = "
Me.TB_A1_ED.SetFocu
Els
Sheets("AutumnHT1").Cells(2, 1).Formula = CVDat
(UserForm1.TB_A1_SDt.Text
Sheets("AutumnHT1").Cells(1, 4).Formula = CVDat
(UserForm1.TB_A1_ED.Text
Unload UserForm
Load UserForm
UserForm2.Sho
Exit Su
End I
End I

End Su

I needed to use the CVDATE function to ensure that it was
copied from the userform to the cell as a valid date
otherwise it was text