# Cal Hours between 35 & 40, and over 40

#### DataFolks

##### 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

#### Attachments

• 8.8 KB Views: 6

#### vletm

##### Excel Ninja
DataFolks
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
@vletm
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.

#### GraH - Guido

##### Well-Known Member
DataFolks
Maybe, using IFS, if available in your version, makes it easier
=IFS(A2>40,A2-40,A2<35,0,TRUE,40-A2)

#### Peter Bartholomew

##### Well-Known Member
Hi Guido (@GraH - Guido)
I have implemented your formula in the form
Code:
``= 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
Code:
``````= IF(hours>lowerLimit, hours-lowerLimit) -
IF(hours>upperLimit, hours-upperLimit)``````

File

#### Attachments

• 63.9 KB Views: 2

#### p45cal

##### Well-Known Member
In B2:
=MAX(0,MIN(5,A2-35))
in C2:
=MAX(0,A2-40)
copied down.

#### 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
Code:
``````= 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!

#### DataFolks

##### New Member
Thank you all for assisting in this. Appreciate you all.