Calulation of hours work and not worked.

=IF(C13>=$C$12,C12,D14-D14)

This what I have so far.  For employees the total hours being 40 for the week.  Employee #1 works 45 hours but took off 2 hours.  Employee #2 works 35 hours, had taken off 5 hours. 

Total hours billable for Employee #1 should be 40 and the (B3) total hours worked is 45(C3) and would ignored the 2 hours off (D3) because he worked overtime to made up for it.

But for Employee #2 he worked 35(C4) hours and did not work 5(D4) hours so that is shown in (B3).

I have the formula in column B, but would like the be 40 minus the absent hour(if any) or stay at 40 other wise.

I have tried to make it clear as i can, any help would be great.

Thanks,

Frank

Hi,

How about this:

=IF(SUM(TotalHours-Absence)>=ExpectedHours, ExpectedHours,sum(TotalHours-Absence))

so this would read:

=IF(sum(45-2)>+40,40,43) which would give 40 as the answer.

In your second example it would read:

=IF(Sum(35-5)>+40,40,sum(35-5)) - which would give 30 as the result.

Alan,

Thanks for answering my question.  I would like to to do a variable to answer the problem. 

I will be entering the time they have work and dont really want to make a new formula every time I enter their time.

Part of the problem is that have to subtract the hours(Absent) from the total hours chargeable, only if hours worked if lower.

Employee works only 35 and takes 5 hours off, and the standard work week is 40 he would would be showing 35 hours for the Chargeable.

So at the end of the week I would enter the 35(Hours Worked) and the 5(Absent) into the sheet and it would auto his chargeable hours to be 35.

Thanks

Frank

Hi Frank,

Based on your anser, I think the attached is what you want.

All you do is enter the name, hours worked and absence, and drag the formula down a row.

Alan,

Thank you very much.  I didn't know you could name a value like that.

I think this will work.  I will try it out.

Frank

Alan,

Ok I think I have another way of explaining it now.

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

Still looking up how to do this in excel.

Frank

What about the new method in the attached?

Does that give you what you want?

Alan,

Only want to minus the hours from the available hours if the Available hours if under 40 for the worked hours.

An example is Jame has to work 40 hours in a week.  In a week he take 2 hours off, but only has 26 hours of work to do.

He would he showing 26 hours of work and 38 hours available.

Frank

Frank,

I think I get it now - what about this?

Alan,

I think the formula does need ELSE and THEN statements.

Because  if hours worked is 44, absent 8 hours, the total should be 40, because he made up the time and then some.  Worked Hours represents time tracked for projects and other tasks. 

Now if the person has tracked worked for only 35 hours was in the office the full for 40 hours and no time off, the total is 40. 

Hi Frank,

This is slightly confusing, so I think we need to define the rules to be applied in a bit more detail.

First off - default hours are 40.

Second - if hours worked equals or exceeds 40 then is the answer always going to be 40?

The confusion I have is with the issue of hours in office, absence, and tracking - what is the correlation between these?

At first I thought it was hours worked less absence and then see if this was less than scheduled, but it seems that tracking comes into it as well.

If you can let me know what the rules are then I can finish this for you - although I am travelling today and tomorrow - back Thursday.

Alan,

Thanks for being patient.

First is is default hours are 40.

Second - if hours worked equals or exceeds 40 then is the answer always going to be 40.  Yes.

Only want to subtract the differance of the hours, and show that in the scheduled hours if they have taken more then that time off.

An example is Mike works 38.5 hours and reports he is out 5 hours, so his his standard hours should be set to the same as his worked hours.

Frank,

Just to be clear - it seems the rules are:

1. If hours worked exceed 40 then charge 40

2. If hours worked are less than 40 then charge the hours worked?

It seems that the absent hours should be ignored?

Alan,

Thanks for all your help. 

Requirements keep changing. 

I will use what you had before now.

 =IF(Hours_Worked>=stdHours,stdHours,Hours_Worked-Absent)