how i can make formula

M

moin

i want to make a formula for time sheet if a employee do over time like
3,4,3.4,3,4 but we have to show 2,2,2,2,2,2 and remaining 9 add in next cell
show as bonus is this possible?
 
J

JLatham

Maybe this will help give you some ideas.

Your original values in row 2, from A2 to F2, as

3 4 3 4 3 4
in G2 (right after last 4), formula is =SUM(A2:F2) and should show 21
in next row (row 3) put this formula into A3
=MIN(A2,2)
and fill that all the way to F3: all should show 2
in G3 the formula is =SUM(A3:F3) and should show 12
in H3 (bonus cell) the formula is =G2-G3

You would always enter the actual hours into row 2 in this example.
 
Top