Excel 2007 : Help using the IF fuction

[tallonarcus]

I need help using the IF function, or I think I need help using the IF function...

I have Five columns; Staff Member, A/L, Public Holiday, Toil Credited and Entitlement Remaining.

A/L and Public Holiday are recorded in days, so when a number of these days are taken they are added to the spreadsheet and the Entitlement Remaining is changed.

The formula that appears in the Entitlement Remaining is "=32-(B2+C2)" as 32 is the total number of days staff are entitled to.

Toil Credited is calculated in hours and not days.

There are 7.5 hours in a working day in my office.

What I want to do is add the Toil Credited to the Entitlement Remaining in a day format.

So, if the staff member puts 7.5 in the Toil Credited cell, 1 is added to the Entitlement Remaining cell. So when the amount reaches 7.5 or multiples thereof, (7.5=1, 15=2, 22.5=3, etc).

I'm not 100% sure if it is the "IF" function I'm supposed to be using here.

But is what I want to do possible?

Any help would be appreciated.

[bmmerkx]

Put in "Entitlement Remaining"-cell (where Toil Credited is in column D):

Please Login or Register  to view this content.

I assume that one could also redeem like 3.5 hours of Toil Credited?

[darkyam]

If it's full days only for whatever reason, then bmmerkx' formula can be easily adjusted: =32-(B2+C2)+INT(D2/7.5)

[tallonarcus]

If it's full days only for whatever reason, then bmmerkx' formula can be easily adjusted: =32-(B2+C2)+INT(D2/7.5)

Hi Darkyam,

That has done what I wanted it to do on the first page, thanks a lot.

I probably didn't explain it correctly; each month has it's own sheet and I want it so that for example in January if the staff member gets 4 hours of toil and in February gets 3.5 hours of toil it adds the one day; I want it so that it carries the total to the following sheets after.

I have copied your formula into the first sheet;

=32-(B2+C2)+INT(D2/7.5)

The second sheet reads;

=SUM(January!E2)-(February!B2+C2)

So I'm not completely sure what this formula, and every formula following, needs to say to make this possible.

You seem to know an awful lot more than me so I hope you can help with this last query!

Thanks again,

Tallon

[darkyam]

Your best bet is probably a couple of columns to deal with the carryover. One can have =Mod(January!D2,7.5) to get the remainder of hours that weren't translated into days and another can add this number to D2 for the current month. Then you would switch the reference for D2 to be this new total in the formula.

Note that for March and following months, the first formula would have to be adjusted to refer to the total hours (i.e., that second column you created) and not the prior month's hours.