VALIDATION PROBLEM

[G-fer]

Hi all,

In cells D15 to D25 and G15 to G25 I need to enter hours worked in the format hh:mm.

By using the normal data validation procedure (Time), I can trap and prevent entries that are not in the format hh:mm. However, the normal validation procedure does not see 4:65 as an illegal entry, but instead forces the cell back from custom hh:mm format to general format, which causes havoc.

I guess I need a VBA routine that will examine the figure entered, determine that it is in the format hh:mm, determine that the "mm" componant is not greater than 59 and, in the case of an error, present a messagebox to that effect.

Could anyone help me please ??

G-fer.

[Bob Phillips]

What DV formula are you using, perhaps we could extend it?

[MaximS]

Hi G-fer,

you can try to format cells in following way Format >> Custom >> [h]:mm

and use Data >> Validation :

1. Tab Settings >> Allow >> Change to Custom
then insert Formula >> =A1<2.5

2.5 is equal to 2.5 days (60 hours)

2. Tab Error Alert >> Style >> Stop
and insert your message.


See example for details.

PS. only range A1:B10 is formated and validated.

[G-fer]

Hi MaximS ... I can see where you're going here, and it would work perfectly well, except it's the minutes (mm) that musn't be allowed to exceed 59.

any other ideas?

Regards ... G-fer.

[G-fer]

Hi XLD ..

My current DV is:

Allow/Time/Between/00:00:00/23:59:59

Regards ... G'fer

[MaximS]

I don't know why you can put more than 59 minutes. I am only able to insert values for hours from 0 to 59 and for minutes 0 to 59. Any other values are bringing me up an error message.

[Bob Phillips]

I get it too, it transforms 4:65 to 5:05, but as a decimal number.

[MaximS]

Yes, you both right guys but it's still correct 4 hours and 65 minutes are in fact 5 hour 5 minutes.

Anyway it want let you put anything giving more than 59:59.

[Bob Phillips]

It is not correct because the user input an invalid amount, it wasn't validated for conformance to standard time rules. It may be correct as far as Excel manages time, but that is garbage as a data validation function.

[MaximS]

xld: I didn't say that was the best method but there is not many possibilities that Excel provides us.

G-fer: I think you can use User Form to validate your input. I know it not the quickiest way but at least is the safest.

Check attachment for details. Run "Data_Input" macro to show User Form.