CLAMP
CLAMP(value, low, high)
Keeps a value between a low and a high limit, such as a ratio held between 0 and 1. It works on numbers, durations and dates, but all three arguments must be the same kind; a bare number among durations counts as working days. If low is above high, the result is high.
Arguments
| Argument | What to give it |
|---|---|
| value | A number, a duration or a date. A blank gives a blank. |
| low | The smallest result, of the same kind. |
| high | The largest result, of the same kind. |
Examples
Formula=CLAMP([Total float], 0d, 10d)
The task's float, with negative float shown as 0 and anything over ten working days as 10: a range a colour scale can use.
Formula=CLAMP([Finish date], DATADATE(), DATE(2026, 12, 31))
The task's finish, held between the data date and the end of 2026.
Formula on WBS rows=CLAMP(SUM(Tasks[Duration]) / 20d, 0, 1)
On a WBS row: the node's total duration as a share of a twenty-day allowance, never above 1.
Reach for something else when
| You want | Use |
|---|---|
| Only a floor, or only a ceiling | MAX(value, low) or MIN(value, high): see MAX, MIN |
| The size of a value whatever its sign | ABS |
Common questions
What does "CLAMP gives Date in one place and Number in another" mean?
The limits must be the same kind as the value. Give a date limits as dates, with DATE or DATADATE.
The editor refuses this=CLAMP([Finish date], DATADATE(), 5)
What does "CLAMP needs numbers, durations or dates" mean?
Text and TRUE/FALSE have no order to clamp to.