Urgent pls. Validation to accept on Date MM/DD/YY format noly. Pls help

[pedie]

Validation to accept on Date MM/DD/YY format noly. Pls helpThanks: Cell value should only take MM/DD/YY format.

Pedie

 

[pedie]

This does not work:

'=format(G12:G17("mm/dd/yy"))

 

[HalfAce]

Hi Pedie,
Are you just trying to set some data validation so it will only accept a date in your specified format?
I'm using 2003 here and I can't find a way for validation to catch that. (I don't know if 07 or 10 has a feature for this or not, mine's at home now taking the day off )

The idea I have is to just use the sheet change event, have it test if the entry is a real date and then format to your specs. (The date is an actual entry, right? - not the return from a formula?)

Does that sound like it might work for what you're doing?

 

[pedie]

Dan, in range G15 I want the user to enter date only in mm/dd/yy format and nothing else...I mean no nother format thanks alot!
Pedie..Sorry steped out for a while

'm using =OR(G12="NA",ISNUMBER(TEXT(G12,"mm/dd/yy")+0)) for now..is this correct?

 

[HalfAce]

Hi Pedie,
(Sorry, for the delay, kinda tied up here today myself.)
Where / how are you using your formula?
Are you trying to use this in data validation or something?

(Is using the sheet change (or calculate) event not an option? I'm thinking whatever format they enter it in, we could just change it to the format you want after the fact.) Will that work for you?

 

[pedie]

I am using this in validation > formula. For now it seems to work well but in case anything comes up latter....lol i'll be .....lol!

 

[pedie]

Dan, sorry I know you're really busy so whenever you're free you can come in and do something here maybe or....i can go on like this for a while unless someone in here has some suggestion...

Thanks again
Pedie

 

[pedie]

Anyone wants to suggest anything regarding this thread?
I am taking this as Unsolved YET:

 

[xenou]

It's not possible really, to fully validate this.
10/11/2010

This is a valid date. Is it Oct 10 or Nov 11? Who knows...

It's best to follow the regional date settings if at all possible, and for data that is being used in international settings, use a date format that is not subject to doubt, such as the following (the last is ISO 8601 and is to be hoped will gain more adherence):

October 11, 2010
11-Oct-2010
2010-10-11

 

[pedie]

Xen, thanks for coming in....
That is okay if it is not possible now yet..like you said it is difficult for formula to understand whether it is a date or month being entered...


Anyways: thanks!


Dan, let's give up here for today....in case you still look here and there...!
Thanks and regards guys!