I am working on a spreadsheet that will make scheduling employees easier.
Cell CB2 is formatted as h:mm, and already contains a formula that returns the number of hours scheduled for an employee for that that day; i.e., 7:00 hours.
I want to put a formula in cell CL2 that looks at the number of hours scheduled for an employee in CB2, and if that number is less than or equal to 5:59 hours, subtract the :15 minute break required by law.
If the number of hours in CB2 is greater than or equal to 6:00 hours, subtract :30 minutes (TWO :15 minute breaks required by law; one in the morning, one in the afternoon).
(In both instances, if there is NOT a value in cell CB2, say because the employee is off that day, I want a blank cell returned, not error messages or zeros. I'm thinking an "If(ISERROR)" formula should be included in the nesting of the main formula.)
Lunch time is not a factor here because it has already been factored out by using multiple start/stop times in previous cells. The times that are added up by the formula in CB2 represent "clocked in" time that the company must pay for, and for which the employee is scheduled to be present.
However, as a manager in a production environment, I need to plan for the number of USEFUL man-hours needed to accomplish production goals. Employees are only concerned with the number of hours they get paid for; managers are only concerned with the number of hours required to get the job done!
I am trying to produce a spreadsheet-schedule that will show both, and the formula has me stumped! Can you help?
I have tried variations of this basic formula but I can't get it to work:
=IF(CB2>=6:00,CB2-:30,IF(CB2<=5:59,CB2-:15,CB2))
Anyone know what I'm doing wrong? Formula? Formatting? HELP!
Thanks in advance.
This message was edited by reefshark on 2002-09-26 20:35
Cell CB2 is formatted as h:mm, and already contains a formula that returns the number of hours scheduled for an employee for that that day; i.e., 7:00 hours.
I want to put a formula in cell CL2 that looks at the number of hours scheduled for an employee in CB2, and if that number is less than or equal to 5:59 hours, subtract the :15 minute break required by law.
If the number of hours in CB2 is greater than or equal to 6:00 hours, subtract :30 minutes (TWO :15 minute breaks required by law; one in the morning, one in the afternoon).
(In both instances, if there is NOT a value in cell CB2, say because the employee is off that day, I want a blank cell returned, not error messages or zeros. I'm thinking an "If(ISERROR)" formula should be included in the nesting of the main formula.)
Lunch time is not a factor here because it has already been factored out by using multiple start/stop times in previous cells. The times that are added up by the formula in CB2 represent "clocked in" time that the company must pay for, and for which the employee is scheduled to be present.
However, as a manager in a production environment, I need to plan for the number of USEFUL man-hours needed to accomplish production goals. Employees are only concerned with the number of hours they get paid for; managers are only concerned with the number of hours required to get the job done!
I am trying to produce a spreadsheet-schedule that will show both, and the formula has me stumped! Can you help?
I have tried variations of this basic formula but I can't get it to work:
=IF(CB2>=6:00,CB2-:30,IF(CB2<=5:59,CB2-:15,CB2))
Anyone know what I'm doing wrong? Formula? Formatting? HELP!
Thanks in advance.
This message was edited by reefshark on 2002-09-26 20:35