PDA

View Full Version : problem with date format



saban
07-04-2006, 12:28 AM
I have txtboxes in which I write date in format dd/mm/yyyy but if I want to put date in cell correctly from this textbox in need to declare event on exit -change format to mm/dd/yyyy and then it puts date in desired format which is dd/mm/yyyy and my regional settings are English(uk)

Is there a way to put any date format in textbox and in cell there will always be format dd/mm/yyyy or at least if date is entered in format dd/mm/yyyy that also in cell the same format is putted

Thnx

asingh
07-04-2006, 01:13 AM
Are you entering the DATE into the txtbox control manually [from the keyboard], or are you..entering the date in an Excel cell, then porting it onto your form...?

saban
07-04-2006, 02:43 AM
in the txtboxcontrol manually and it is putted into cell

Cyberdude
07-04-2006, 01:16 PM
Can you make use of something like
Range("A1").NumberFormat = "dd/mm/yyyy"
following the statement that writes the value on the worksheet?

asingh
07-05-2006, 03:11 AM
Hi,

You could try the following:

As soon as the user enters values into the textbox, you could use the KEYUP event to force date entry, and when the user exits the text box, you could use ISDATE to check if the values entered is infact a valid date. If ISDATE returns to be "true", then the textbox date value can be placed on your excel cell, else if ISDATE returns false, the textbox value resets, and the users get a message box --- "Value entered is invalid...!".

Let me know if this helps, if need be, I can provide a working sample too.

thanks and regards,
asingh

saban
07-12-2006, 01:04 AM
Thnx I will try that and plizz can u get me working example
And one more question how is it done that according to regianal setting the date is putted into cell

(ex. If regional settings are set to US, then even if I write date in textbox like 25/06/2006 the date format putted into cell should be 06/25/2006)

Thnx

geekgirlau
07-14-2006, 03:52 AM
One of my major gripes using any Microsoft product outside of the US is the constant battle with date formats.

To make sure there is absolutely no confusion, I often format the textbox as "dd-mmm-yyyy" once the value has been entered. Then at least if the value is transposed due to regional settings, it's immediately obvious to the user. You then insert that value into the cell, and format the cell for whatever date format you want to use.

asingh
07-15-2006, 06:06 PM
Hi Saban,

First of all apologies for such a late reply, when I promised a working example..!

Attached below is a sheet, see if it helps. I have restricted the keyboard, so non numeric characters can not be entered in the text box. [Except for "/"]. When the Submit command button is clicked, a date check is done, and if the date is valid, it is outputted to cell 'A1'. [Format dd-mm-yyyy].

regards,

asingh

mdmackillop
07-16-2006, 12:29 AM
it is outputted to cell 'A1'. [Format dd-mm-yyyy]

Your date is being outputted as a string, not a numerical date; try 01/01/06 it appears left formatted and not with 2006 as the year.
Change to
Range("A1").Value = DateValue(TxtBxVl)
Range("A1").NumberFormat = "dd/mm/yyyy"

There is nothing to indicate date entry order. 01/25/06 will be correctly interpreted as there is no alternative, but what about 6 September or 9 June. You could add a label to your form to show "d mmm yy" format.