Custom Validation

[Mark McDonough]

I have a timesheet set up in Excel which has the following data all in
reference to row 9:

Column C = Start time eg 08:00 AM
Column D = Lunch time eg 1:00 hr
Column E = End time eg 19:00 PM
Column F = Total Time (caluculated) =E9-C9-D9
Column G = Overtime (calculated) =F9-"7:00" (normal hours are 7)
Column I= Meal Allowance

The meal allowance is only payable for overtime exceeding 1 hour and is a
maximum of $15.

I want to enter a custom validation to only allow input for the meal
allowance when Column G > 1 hour and then only for $15. What is the
validation formula I should use.

I have created the following formula but it doesn't seem to be doing the
job:

=if(G9<="1:00",I9=0,and(G9>"1:00",I9=15))

All times are in the format hh:mm.

Any help greatly appreciated.

XL2003 and WinXP

 

[Ardus Petus]

=AND(G9>"1:0",I9=15)

HTH

 

[Guest]

You can only use the > if you are using a numeric value - because your value
is enclosed in quotes "1.00" it is text and thus cannot be identified as a
number.

I would be inclined to use the IF function as a formua to calculate the
actual value for I9 based on hours worked etc rather than Data Validation
which only checks input. HTH

Sheila

 

[Mark McDonough]

Thanks Ardus but it doesn't work for all possibilities. For example if I
have overtime = 0 in G9, this validation doesn't allow for an input of 0
which it must.

Also where the overtime is greater than 1 hour, this validation prevents an
input of $15 which is the entitled meal allowance. I really think an if
statement is required here to say:

If overtime in G9 = from 0 to 1 hour then restrict input in Meal Allowance
to 0. (in other words no meal allowance).
and
If overtime in G9 is greater than one hour, then allow an input of $15 only.

C9 to G9 is hh:mm
I9 is general format
I'm not sure if I have some issues around time formulas etc