Need formula to calculate FTE's: Full Time Equivalents.

reefshark

New Member
Joined
Sep 25, 2002
Messages
4
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top