Date Format in User Defined Forms Text Box in Excel 1

[jlf81]

Hi,

I am trying to change the format of the date in a text box within a user defined form in excel, currently it is showing as mm/dd/yyyy, I need it to show as dd/mm/yy.

If anyone could point me in the right direction it would be helpful as I can't see where the settings for this are held. Also I am using excel 2003 incase it has changed over the versions

Thanks in advance

Josh

 

[HarleyQuinn]

Try looking at the Format() function.

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[jlf81]

I can't see any function for the format in the properties window for the object, or should I be looking to use format () in the code somewhere?

Thanks

Josh

 

[SkipVought]

You must convert the Date/Time value to a STRING. If you are using sheet functions, the function is the TEXT function. If you are using VBA, the function is the Format function.

I would strongly suggest that you either use a Calendar date picker for the user or have the user enter year, month and day in separate textboxes. Otherwise you can have a validation monstrosity to handle.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

second what Skip says - have had to write some horrible code in the past to try to deal with determining the format a suer has entered data into a textbox in...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

For instance the user enters...
[tt]
01/02/09
[/tt]
is it...

Jan 2 2009
Feb 1 2009
Feb 9 2001

???

Any of the three is a valid way of interpreting what was entered.

As far as year, month, day formats, YMD with a FULL YEAR, is unambiguous. Any other entry format can be open to ambiguity.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jlf81]

Thanks for your help I will look into the date picker you sugguested, as I normally want to return todays date, it's only in exceptional cases I wish to change it.