excel timesheet nested if statements

[Liz]

I have pondered this question for several years and asked people at work with
excel experience and no one can help.

The problem is I need a function that will look at if the total is 37.5 or
less nothing is to be done, but if the total hrs is full time (40 hrs) or
less and above the reg work week of 37.5 hours then regular comp is awarded
1/4 hr per 1/4 hr.
If the time is over a reg 40 hours then this time needs to be seperated to
multiply by 1.5 hrs of comp.
The nested if function is if the total of g1:g7 (g8) is >37.5 but <= 40
subtract (g8)-37.5 if not then 40-37.5

then the next line would be if the total of g1:g7 (g8) > 40 then g8-40.
if((sum(g8)>40),sum(g8-40),"")

 

[Peo Sjoblom]

This will give you time over 40 hours

=MAX(G8-40,0)


this will do the 40-37.5

=MIN(MAX(0,G8-37.5),2.5)


this assumes that you use decimal hours like 8.0 and not excel hours like
08:00
if you do that can be fixed but since I saw you use 37.5 I assumed you are
using decimal hours