Need help with a formula

[BruceK]

I need help figuring out a formula? Here is what I want to do

I want one cell to add up a column and if that number dosent reach 40 then
it takes more away from another cell but it only displays the 40 or lower and
then anything over the 40 will be displayed in another cell

 

[Lars-Åke Aspelin]

I need help figuring out a formula? Here is what I want to do

I want one cell to add up a column and if that number dosent reach 40 then
it takes more away from another cell but it only displays the 40 or lower and
then anything over the 40 will be displayed in another cell

Please explain in more detail.
Give an example of input and expected output.

How many cells are involved in this?

Are the firstly mentioned "another cell" the same cell as the secondly
mentioned "another cell".?

What do you mean by "it takes more away"?

Lars-Åke

 

[BruceK]

Ok here is what I have I am doing up a timesheets here in Fl I will be having
two colums for hours worked. ( Reg & OT) the company says that anything over
40 hours awekk is overtime and not untill you hit 40 hours but they also want
all over time in seprate columns

What I want to do is have reg time in one column and OT in another for each
day then at the bottom of the week total hours for each column and then below
that a row with what they would be paid on So one cell would show 40 hours
or less if no OT was done that week and other would show the over time(what
is over 40) if they had a combined Work time that was more the 40 hours

 

[Dick Kusleika]

I need help figuring out a formula? Here is what I want to do

I want one cell to add up a column and if that number dosent reach 40 then
it takes more away from another cell but it only displays the 40 or lower and
then anything over the 40 will be displayed in another cell

=MIN(40, SUM(A1:A100))

 

[Lars-Åke Aspelin]

Assuming you have some date information in column A and the actual
hours worked in column B.
Assuming row 1 is used for some header information and that rows 2 to
8 hold the data for one week.

Try this formula for the Regular time:

=MIN(40,SUM(B2:B8)

and this formula for the Overtime:

=MAX(0,SUM(B2:B8)-40)

Hope this helps.


With the following test data in B2:B8

6.5, 8.7, 7.1, 4.5, 9.8, 1.5, 0

the result will be 38.1 for regular time and 0 for Overtime

With the following test data in B2:B8

8.4, 6.9, 10.5, 11.0, 8.5, 4.0, 3.0

the result will be 40 for regular time an 12.3 for Overtime

Hope that is what you expect. / Lars-Åke

 

[BruceK]

Dick Thank you is there a way to make it so it will only show postive (over
40 hours ) in another cell?

 

[Gary Keramidas]

not sure, but this may help:

=MOD(SUM(A1:A100),40)

 

[BruceK]

Thank you all with all your help I got it to work

 

[Lars-Åke Aspelin]

If the sum of worked hours is e.g. 85, then the overtime is 45 but the
MOD formula only gives 5 so it does not work if the overtime is 40 or
more.

Lars-Åke

 

[Gary Keramidas]

wasn't figuring anyone was working over 80 hours