If you've ever tried to create a Formula column using a Duration column, you will have noticed that it's currently not possible. So how do you work around it?
This was a curious quirk that I came across earlier this week. I wanted to perform rounding on a Duration column and found that it wasn't possible. The Duration column that I'd already created didn't show up in my formula builder and if you type the logical name manually, you'll see an error like this:
However, with an extra field in between the two, you can work around the limitation:
- Create a Calculated Whole Number column that references the Duration column.
- Reference the Calculated Column in your Formula Column.
You'll see that the column I created in step one, 'Calculated Duration', is now available to use in my formula, even though it's referencing a Duration Column
Ultimately, you'll end up with three columns, your original Duration column, the Calculated Whole Number column and your Formula column:
So it's possible to use Duration columns in Formula columns - it just seems that Microsoft don't want you to.. for now at least.