Calculation of hours worked but with IF and ELSE statements.

**Redmars7** · 01-19-2012, 12:40 AM

My boss has me trying to figure out how to report the hours for people.

Set billable time for the month is 120 hours.
Tommy works 130 hours, but still takes 8 hours off, and still worked 130 hours. So he made up the time, but still took off the 8 hours.
Next Jim takes 4 off and only works 116 hours, he did not make the 100% of the hours needed for the week.

I'm trying to figure out how to use the IF and ELSE to get the correct data calculated in the sheet, still learning how to do the formulas.

For the Possible Billable Hours have the Out Of office hours to be subtracted if they are under the HOURS WORKED, (If they are over the Hours Worked, ignore Out Of Office Hours.) and insert total in column D.

Examples:

So Tom worked 130 hours even though he was out of office 8 hours(He made up the time).

Jim did not work the full monthly hours, only 116 hours and took off 4 hours. So he would have been only billable for 116 hours.

The last column would be the % calculated for that person, I was =SUM(Hours Worked/Monthly hours). But not sure if showing the right representation, like for Jim he worked 100% of the time he was in the office.

Attached is an rough example, excel 2010.

Any push in the right direction would help.

Thanks

test.xlsx

**JBeaucaire** · 01-19-2012, 01:08 AM

Originally Posted by Redmars7

So Tom worked 130 hours even though he was out of office 8 hours(He made up the time).

Your sheet does not demonstrate how you know he was out of the office AND he made up the time. I know because you told be on the forum. All your data must be represented in your table.

Meanwhile, if each employee has a potential billable hours of 120, then the formula you could put in F5 and then copy down is:

=E5/120

As for your total in F4, I'd think the maximum would be 100%, so would this F4 formula work for you:

=MIN(AVERAGE(F5:F8), 1)

**bebo021999** · 01-19-2012, 01:11 AM

You said:

Originally Posted by Redmars7

For the Possible Billable Hours have the Out Of office hours to be subtracted if they are under the HOURS WORKED, (If they are over the Hours Worked, ignore Out Of Office Hours.) and insert total in column D.

But:

Jim did not work the full monthly hours, only 116 hours and took off 4 hours. So he would have been only billable for 116 hours.

Billable of Jim should be 116-4=112

So, D5:

Please Login or Register  to view this content.

F5:

Please Login or Register  to view this content.

Let me know if it meets your boss'

**Redmars7** · 01-19-2012, 09:55 AM

bebo021999

Yes this is forumula is a great start. But Don't want it to add the hours from the Hours Worked to the billable hours. 120 hours is the let say the is bar that people have to get too, if they work over time that is shown in the Hours worked.

So if Tom is scheduled to work 120 hours but works 130, but still takes off 8 hours. So show showing the billable hours = to the Monthly hours 120. Only taking off the out of office hours if hours worked if below the the Hours worked total.

Thank you for your help. I'm still looking at also

-RM

**JBeaucaire** · 01-19-2012, 11:25 AM

Did you look at my suggestions?

**Redmars7** · 01-19-2012, 11:52 AM

JBeaucaire,

Thank you for your suggestions. It not that I want to show 100% for the total I want to show the work that the person did. Sorry I was not clear on what I was asking.

-RM

**Redmars7** · 01-23-2012, 01:28 AM

I think this may be close to what I am looking for, looking how to do this in excel, just not good at formulas, yet.

=IF(WH > SH) THEN (SH) ELSE (SH-OH) ENDIF

If any one know of good site for learning how to do formulas in excel so get more experience, that would be great.

-RM