Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Validation
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Validation
=AND(G9"1:0",I9=15)
HTH -- AP "Mark McDonough" a écrit dans le message de news: ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Validation
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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Validation
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 "Ardus Petus" wrote in message ... =AND(G9"1:0",I9=15) HTH -- AP "Mark McDonough" a écrit dans le message de news: ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation - Custom | Excel Discussion (Misc queries) | |||
relative cell reference in custom validation | Excel Discussion (Misc queries) | |||
Custom data validation | Excel Discussion (Misc queries) | |||
Data Validation - Custom | Excel Discussion (Misc queries) | |||
Using custom functions within custom validation | Excel Discussion (Misc queries) |