Is there a Excel formula that after a certain value is reached surplus value is shown.

Question

Hello community.

I tried to solve my employee hour problem in excel.

The issue is that most of my workers work more than 7.5 hours per day but to the government doesn't allow to do so. So I have to make a recalculation and send it to the tax agency.

Basically I have an excel file with all the employees and their hours. I need a formula that when 7.5 hours is reached per day all the left over hours are shown.

For example if the worker has worked 6.5 hours that day the formula doesn't do anything.

But if he has worked f.e. 12 hours, it has to show 7.5 hours and 4.5 surplus.

Answer 1

Hi Martins,

Your question:

Is there a Excel formula that after a certain value is reached surplus value is shown.

There is more than one way to skin this cat. But let's Keep It Simple:

I would use 3 columns:

1. Total Hours (the actual hours worked)
2. Adjusted Hours (reports either actual hours or stops at 7.5 hours)
3. Surplus Hours (reports either 0 or a surplus)

For formulas:

1. Total Hours = whatever you already have in place is fine
2. Adjusted Hours =IF(C3<=7.5;C3;7.5) assuming Total Hours is in Column C Line 3 (adjust as needed to match what you have)
3. Surplus Hours =C3-D3 again, adjust for your columns and lines

The nice part here is that you do not lose the original data and you can change your cap values as things change.

Su