Time format

[ashnaveen]

Hi forum
I have a userform which consists of Date and time field.
By pressing the submit button, I want the code to check if the user has filled in the right format ie., mm-dd-yyyy for Date and hh:mm for time.

Can anyone plssssss tell me how to do this?

Thanks in advance
Ash

[ALe]

if your data are coming from cells, you can check if Numberformat is equal to "d/m/yyyy h:mm"

[Bob Phillips]

Hi forum
I have a userform which consists of Date and time field.
By pressing the submit button, I want the code to check if the user has filled in the right format ie., mm-dd-yyyy for Date and hh:mm for time.

Can anyone plssssss tell me how to do this?

Thanks in advance
Ash

For the date, why not just test the input foir a valid date? The format is almost irrelevant, as it is stored as a serial number nothing to do with the format. As for time, as long as it is between 0 and 1, it is a valid time. Again, it is just stored as fractional n umber.

[Zack Barresse]

Hi there Ash,

Are these two fields seperate, or entered in the same field? If the same field, use the IsDate() VBA function, then the date is only the Int() (integer) of that field and the time is only the fraction (if one - there doesn't necessarily need to be one, ie midnight).

[Cyberdude]

I agree ... check to make sure it is a date, then you can reformat the value to be whatever you want using NumberFormat. Then it doesn't matter what the user wrote as long as it's a valid date.

[ashnaveen]

Yes, Date and Time are 2 different fields.
Now, with isDate() function, I'm able to check for the right entry.
With the time format, I again have 2 fields here. One for hours and the other for minutes.
I jus need to check if the user in entering numbers and not strings. How do I do this?

[ALe]

there's a function called IsNumeric that could be of help

[Zack Barresse]

Check out the DateSerial and TimeSerial functions. Plug in your data fields where applicable to build a "real" and "excel recognized" date and time.

[Aussiebear]

Does this mean that some one could simply type in 3.5 as a value in a time formatted cell and it would convert it to 03:30 if the format was hh:mm?

Ted

[Bob Phillips]

No, he is looking for a validator, not a convertor.

[Aussiebear]

Okay, will post a new thread rather than hijack this one.

[lucas]

an example form that validates and then formats the cell....date only in the example.