IF Argument to Record Sick Hours

[Meepster]

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, but recording the Used Hours if still remaining.
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.

 

[mkwilli2]

My first question is if B4 has the formula: B1-B2-B3 why in your example does it say 16 if 24-4-2 is 18? Was this just a typo?

As for your IF statement I believe the following will work:
=IF(B3>=24,0,MIN(B1-B3,B2))

Hopefully that works for you!