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

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 wrote:

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

Dave Peterson wrote:
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 wrote:
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

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

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

"Lynz" wrote in message
...
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

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

"Lynz" wrote in message
...
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


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

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.
--
David Biddulph

"Lynz" wrote in message
...
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

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

"Lynz" wrote in message
...
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

David Biddulph wrote:
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.
--
David Biddulph

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



a

"Lynz" wrote in message
...
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

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

"Lynz" wrote in message
...
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

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.
--
David Biddulph

"Lynz" wrote in message
...
David Biddulph wrote:
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.
--
David Biddulph

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



a

"Lynz" wrote in message
...
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

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

"Lynz" wrote in message
...
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

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 wrote:

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

"Lynz" wrote in message
...
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


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

--

Dave Peterson