Cal Hours between 35 & 40, and over 40

[DataFolks]

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

 

[vletm]

DataFolks
Would You use few minutes to study:
IF function

IF function - Microsoft Support

How to use the IF function in Excel to make logical comparisons between data values.

support.microsoft.com

AND function

AND function - Microsoft Support

Syntax: AND(logical1, [logical2], ...)

support.microsoft.com

Using IF with AND, OR and NOT functions

Using IF with AND, OR, and NOT functions in Excel - Microsoft Support

How to use the IF function (combined with the AND, OR, and NOT functions) in Excel to make logical comparisons between given values.

support.microsoft.com

After You've read and studied those page, You should able to use those functions too.

 

[Peter Bartholomew]

@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.

 

[vletm]

for Peter Bartholomew
Did You noticed those three If sentences?

 

[GraH - Guido]

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]

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]

File

 

[p45cal]

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

 

[Peter Bartholomew]

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!

 

[DataFolks]

Thank you all for assisting in this. Appreciate you all.