Date format changes when inputed from user form

[Lynz]

Hi, I have a user form which contains text boxes into which I type the
date in format dd/mm/yy. However when I click on the enter button and
the data is entered onto my spread sheet the date enters as mm/dd/yy. I
can get it to enter in the correct format if I use a calendar to add the
date but I dont want to do this If I can help it. Is there an easy
solution to this problem. I have spent hours trying to get it right but
nothing Ive tried works.
Thank you for any assistance
L

 

[Dave Peterson]

I would use something different than a textbox.

Either a calendar control...

Ron de Bruin has some notes:
http://www.rondebruin.nl/calendar.htm

Or even 3 textboxes/comboboxes/spinners/scrollbars.
One for the month, one for the day and one for the year.

 

[Lynz]

Format/ Cells/ Number/ Date and choose an appropriate date format
or
Format/ Cells/ Number/ Custom if you want to use a less standard option.

Thank you for the quick reply, I have tried Formating as date, text and
custom and if I put 10/12/08 in my text box it still comes out as
12/10/08 on my spread sheet.
L

 

[Lynz]

I would use something different than a textbox.

Either a calendar control...

Ron de Bruin has some notes:
http://www.rondebruin.nl/calendar.htm

Or even 3 textboxes/comboboxes/spinners/scrollbars.
One for the month, one for the day and one for the year.

Thanks, but I was trying to keep it simple, maybe ill just add "todays
date" and modify if necessary. Sometimes it will be todays date and
other times it will be a previous days date depending on how up to date
my data entry is.
L

 

[Dave Peterson]

The problem is with text that looks like a date using the user's windows
regional settings short date order (mdy or dmy or ...).

If your program sees
1/2/3
in a cell, excel will use whatever shortdate format order that the user is using
to parse the entry.

If it's mdy, you'll end up with Jan 2, 2003
If it's dmy, you'll end up with Feb 1, 2003
If it's ymd, you'll end up with Feb 3, 2001

And if the text box looks like:
31/12/2008
and the user is using mdy order, then you'll end up with a string--it won't even
be a date.

I guess if you always know that the order in the textbox is dmy, you could parse
the entry into 3 pieces and create a real date from that.

But I don't know how you would know what the user meant when an ambiguous string
is entered.

 

[Lynz]

That sounds as if it isn't a problem with the formatting of the date
display, but with the interpretation of the data you are putting in. To be
unambiguous, display the data (at least for the time being) as something
like dd mmm yyyy. If you are seeing 12 Oct 2008 when you wanted 10 Dec
2008, the problem isn't in your Excel display format, but in your Windows
Regional Settings, which you can get at and change through the Control
Panel.

I have been into the settings in control panel and short date is
dd/MM/yy , long date is ddd/mmm/yyyy. IT was a bit hard to tell what it
was set as as today is 12/12/08 as luck would have it. Anyway tried my
form again and 10/12/08 came out as 12/10/08, looks like I will just
have to use a calendar which I have tried before on another form and it
inputs the correct date. I only use dd/mm/yy as it is quick and easy to
type.
Thank you for all the replies of help, much appreciated.
Merry xmas L

 

[Lynz]

If I were you I would recheck your settings carefully, both your Regional
Settings and your Excel cell format.
You didn't respond to my suggestion that you should format the cell
temporarily as something like dd mmm yyyy.
If your Regional Settings are really dd/MM/yy, then if you input 10/12/08
you should NOT be seeing 12 Oct 2008; you should be seeing 10 Dec 2008. Are
you sure that you are seeing 12 Oct 2008 when you input 10/12/08?
If you see 12 Oct 2008 then it is your regional settings that are wrong. If
you see 10 Dec 2008, then to see it as 10/12/08 you just need to set your
cell format to dd/mm/yy.

Hi David thanks again for replying
I set the format for the cells to dd/mmm/yyyy and entered 10/12/08 in my
user form. This entered the date as 12/Oct/2008. If I enter the same
thing directly into the cell it comes out as 10/Dec/2008 which is
correct, it is just changing when entered via the user form.. very
confusing. By the way, my macro inserts a new row at the top of my data
after adding my data so that it is always on the top. Would this change
the formatting?
L

 

[Lynz]

Hi David thanks again for replying
I set the format for the cells to dd/mmm/yyyy and entered 10/12/08 in my
user form. This entered the date as 12/Oct/2008. If I enter the same
thing directly into the cell it comes out as 10/Dec/2008 which is
correct, it is just changing when entered via the user form.. very
confusing. By the way, my macro inserts a new row at the top of my data
after adding my data so that it is always on the top. Would this change
the formatting?
L

Just replying to myself here but this is the code that adds my data.
The dates Im having trouble with are in text box 21. Do I need to add
some formatting here somewhere maybe??
L

Private Sub CommandButton8_Click()

ActiveWorkbook.Sheets("Bulling").Activate
Range("A3").Value = Format(Now, "dd mmm yyyy")
Range("B3").Value = ComboBox6.Value
Range("C3").Value = TextBox21.Value
Range("E3").Value = TextBox22.Value
Range("G3").Value = ComboBox5.Value
Range("H3").Value = TextBox23.Value
Range("A3:Z3").Select
Selection.EntireRow.Insert
Range("A3").Select
ComboBox6.Value = "Cow"
TextBox21.Value = "Bulling 1"
TextBox22.Value = "Bulling 2"
TextBox23.Value = "Comments"
ComboBox5.Value = "Red Devon 488"
ComboBox6.SetFocus


End Sub

 

[Dave Peterson]

Something to try:

Option Explicit
Private Sub CommandButton1_Click()
With ActiveSheet.Range("A1")
.NumberFormat = "mmmm dd, yyyy"
.Value = CDate(Me.TextBox1.Value)
End With
End Sub
Private Sub UserForm_Initialize()
Me.TextBox1.Value = "11/12/2008"
End Sub


But I still don't know how you as the developer will know what the user meant
when he/she typed 11/12/2008.

 

[Lynz]

Something to try:

Option Explicit
Private Sub CommandButton1_Click()
With ActiveSheet.Range("A1")
.NumberFormat = "mmmm dd, yyyy"
.Value = CDate(Me.TextBox1.Value)
End With
End Sub
Private Sub UserForm_Initialize()
Me.TextBox1.Value = "11/12/2008"
End Sub


But I still don't know how you as the developer will know what the user meant
when he/she typed 11/12/2008.

Thanks Dave P and others, Ill see if that will help, havent tried it yet.
ps Im not a developer, Im a "Farmer", but I love working with excell. I
am the only person inputing the data and as I live in NZ I always use
dd/mm/yy. Maybe I need to change my date entering habits and go to
dd/mmm/yy or something instead. I am using the form to keep a track of
when my cows calve, when they come back on heat, when to check them
again in 21 days time,the date they were served,and when the expected
calving date will be next year etc. I hope I am not wasting your time.
L

 

[Lynz]

Hi, Ive got it to work and enter the correct date at long last.

Private Sub TextBox21_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox21.Text = Format(TextBox21.Text, "dd/mmm/yy")

End Sub

Is it ok to do this? seems to work. Thanks for all your assistance.
Cheers L

 

[Dave Peterson]

I would have used cdate().

Hi, Ive got it to work and enter the correct date at long last.

Private Sub TextBox21_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox21.Text = Format(TextBox21.Text, "dd/mmm/yy")

End Sub

Is it ok to do this? seems to work. Thanks for all your assistance.
Cheers L

 

[Dave Peterson]

ps.

But I bet any unambiguous date will work ok.

Hi, Ive got it to work and enter the correct date at long last.

Private Sub TextBox21_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox21.Text = Format(TextBox21.Text, "dd/mmm/yy")

End Sub

Is it ok to do this? seems to work. Thanks for all your assistance.
Cheers L

 

[Lynz]

I would have used cdate().

Thanks Ill look that up.