• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cal Hours between 35 & 40, and over 40


New Member
Can someone help me develop a formula? If an employee's weekly work hours are more than 35, in one column list the number of works worked between 35 and 40, and in another column for any hours worked over 40 in a week, list the number of hours that exceed 40.

If it's less than 35, return 0
If it's between 35 - 40, return the difference between 40 and 35 (positive numbers)
If over 40, return hours worked over 40



Excel Ninja
Would You use few minutes to study:
IF function
AND function
Using IF with AND, OR and NOT functions
After You've read and studied those page, You should able to use those functions too.

Peter Bartholomew

Well-Known Member
I am not convinced that the calculation is as trivial as you suggest. There are several ways of structuring it. One that create an array of all relevant values is shown in the figure


Of course, focussing on one column at a time simplifies the formula because tests against 0 and ∞ are not needed for the first and final columns.

Peter Bartholomew

Well-Known Member
Hi Guido (@GraH - Guido)
I have implemented your formula in the form
= IFS(hours>40, hours-40, hours<35, 0, TRUE, 40-hours)
Setting the step length in my chart to 0.1 hrs, it gave:

All the right notes but not necessarily in the right order?

I will attach the file on the assumption that you now have access to Excel 365 but, as yet, not the LET function.
I have removed the annotation provided by LET to give my formula as
= IF(hours>lowerLimit, hours-lowerLimit) -
  IF(hours>upperLimit, hours-upperLimit)

Peter Bartholomew

Well-Known Member
I added @p45cal's first formula to the dynamic chart. I replaced the hard-wired values by the named parameters so that the formula matches the others as the parameters are adjusted. The real catch is that MIN and MAX do not work as required with array parameters so the formula became
= IF( IF( upperThreshold<hours, upperThreshold-lowerThreshold, hours-lowerThreshold) > 0,
      IF( upperThreshold<hours, upperThreshold-lowerThreshold, hours-lowerThreshold),
      0 )
That's the bad news; the good news is that it matched my 'lower band' curve exactly!