Calculating Sick Hours

Greetings Excel Wizards.

I am unable to create a formula to calculate employee sick hours correctly:

----A-----------------------------B------
1--Avail Hours--------------------24------
2--Current Used Hours-------------4-------
3--Prior Used Hours----------------2------
4--Remaining Hours----------------16-----
5--Notify Payroll of Used Hours------4-------


Some context, B1(Avail Hours) is static at 24.
We manually enter the current used hours(B2) and prior used hours(B3).
Remaining Hours (B4) is simply =B1-B2-B3
This issue I have is with B5. The value needs to make sure employees are paid their sick hours and becoming a zero value when exceeding 24hrs.
Example: Current Used hours is 20, Prior Used is 2, Notify Payroll should show 20 hours to report.
Example 2: Current Used hours is 20, Prior Used is 20, Notify Payroll should show 4 hours to report.
Example 3: Current Used Hours is 20, Prior Used is 24, Notify Payroll should show 0 hours to report.

I have tried an IF function but failed miserably.
Hopefully an excel wizard can assist me in this matter, I would greatly appreciate it.

Meepster.

Use the MAX function, that is

Formula:

Please Login or Register  to view this content.

Thanks for your suggestion. However this formula only calculates the difference between remaining hours and the 24 allocated hours. The Current used hours (B2) need to be reported in B5.
Any other ideas you could provide would be great.

I believe I solved it with the following formula:

=IF(B2+B3<=B1,B2,B1-B3)

Ah yes, missed that bit.

Yup, your formula should work. It's equivalent to

Formula:

Please Login or Register  to view this content.