SharePoint calculated column with 3 conditions

0
votes

I have a SharePoint list like: SharePoint List

"All Hours per Shift" are calculated from =IF([Time Out]="","0",TEXT([Time Out]-[Time In],"h")).
Then breaks need to be deducted. My "Hours per Shift" are calculated from "All Hours per Shift" with formula like:

=IF([All Hours per Shift]<4,[All Hours per Shift],IF(AND([All Hours per Shift]>4,[All Hours per Shift]<11.9),[All Hours per Shift]-0.5,IF([All Hours per Shift]>11.9,[All Hours per Shift]-1.5)))

I need:
AllHoursPS <4 do nothing
AllHoursPS >4 and <11.9 then -0.5h
AllHoursPS > 11.9 then -1.5h

Both columns, "All Hours per Shift" and "Hours per Shift" are Numeric columns with 1 decimal place.

Unfortunately, the formula always deducts 1.5h. Why?

Could someone help, please?

Thank you.

Slawek

1
if-statementsharepointcalculated-columns

1 Answers

0
votes

What's the type of the column "All Hours per Shift"?

Assuming it's a number column, you could use this formula:

=IF(value([All Hours per Shift])<4,[All Hours per Shift],IF(AND(value([All Hours per Shift])>4, value([All Hours per Shift])<11.9),[All Hours per Shift]-0.5,IF(value([All Hours per Shift])>11.9, [All Hours per Shift]-1.5)))

enter image description here