M
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
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