This is a discussion on Need formula to calculate FTE's: Full Time Equivalents. within the Excel Questions forums, part of the Question Forums category; I am working on a spreadsheet that will make scheduling employees easier. Cell CB2 is formatted as h:mm, and already ...
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 ]
Hi - welcome to the board.
You need to read up on how excel stores / calculates dates and times - sticking things like :30 into the formulas isn't going to help!! Check out the excel file for 'How excel stores dates and times', then see here:
http://www.ozgrid.com/Excel/
for more info (click on the introduction link & find the dates / times page). Then see here:
http://www.mvps.org/dmcritchie/excel/datetime.htm
for all sorts of info.
That said, try:
=IF(CB2< 6/24,CB2-1/96,IF(CB2>=6/24,CB2-1/48,CB2))
Paddy
EDIT - correcting the < and > in the formula...
[ This Message was edited by: PaddyD on 2002-09-26 20:43 ]
Like this thread? Share it with others