Limit value sum formula

Here are two formulas that should do the job.

For the example, I've placed the "Total Hours" values in column E to show the results for the two formulas with several different totals. Each row is a separate example of the calculations done for the weekly total shown in that row.

Formulas shown below the table are from cells F2 and G2 respectively.

In use you would need a cell (D2 in the examples) containing the sum of the daily hours, and one copy of each of the formulas illustrated above, with the cell reference to the cell containing the weekly total hours on your table replacing D2 in the first example above OR with a reference to the range of cells containing the daily hours replacing D2 in the second example.

F2: MIN(D2,"43h")

G2: MAX("0h",SUM((D2)−"43h"))

Regards,

Barry

PS: You may need to replace the values in quotes with DURATION(0) and DURATION(,,43) if you get an "expects a duration" error message.

Hi Darin.

This formula, intended to return the "regular' hours should be returning the minimum of the two values, one supplied and the other calculated,not the maximum. As written, it will return 43 for any number of hours worked up to and including 43 hours, and the actual number of hours worked for any SUM greater than 43.

Change MAX to MIN to return the expected value.

One other change in the formula:

The syntax for SUM is SUM(value1,value2,…)

Your content for that function is value1+value2…

value1+value2… is an arithmetic sentence which returns a single value. making your SUM statement essentially SUM(value 1).

Either remove SUM( and ) from the formula, or replace the + signs with commas,

My recommendation would be to keep SUM(…) and separate the individual cell references with ,

Your formula would then become:

MIN(43,SUM(C4,C6,E4,E6,G4,G6,I4,I6,K4,K6,M4,M6,O4,O6)

Regarding the second formula:

This one should return the correct value as written. For consistency, I would write it and the first formula using the same format for adding the individual hours worked by either changing this one to follow the SUM() syntax or by removing SUM() from the first and using the addition format used in this one.

An alternative, which would reduce the calculation load on the document would be to sum the total working hours in one cell, then use a reference to that cell in place of the SUM or addition sentence in these two formulas.

Regards,

Barry

Hi Darin,

You are welcome.

Thank you for the green checkmark.

And don't worry about being a 'total newb.' There's no shame in that, and we've all been there at some time!

I appreciate the clarity of both your topic statement (title0 and your question. Made it much easier to answer than some of the others. :-)

Regards,

Barry