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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 01:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com