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.
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.