Sorting calculated fields in SharePoint

Some people might ask why I even maintain a blog if I don't write in it regularly or advertise it to followers. Of course, I don't have enough followers for anyone to even ask that - as of right now, I have one! If you're the guy following me, thanks!

Anyway, the main reason I do this is because I want to write about the weird cases - the things that happen in a 1-in-1000 chance that I'm unlikely to remember again (but inevitably have to revisit within a couple of years). The things that I spend more than 10 minutes on Google trying to even find something relevant to my problem, let alone a solution.

The things like I'm writing about today.

User has a list of tasks with due dates. Not all the due dates are populated. He's not worried about those tasks yet, he only wants to know about the ones that are actually due. Fair enough. How do we change the list sorting so that SharePoint puts the blanks in the back?

The normal solution would be to create a calculated field that references that date field and substitutes a sufficiently high/low value for the blank to make a surreptitious field that does what I want.

The only problem is - while it produces the calculated result I want, SharePoint still sorts however it feels like doing!

Thanks to this post by Wesley Bakker, I am reminded that regardless of the output type you select for the calculated field, SharePoint sorts before displaying the value (this makes sense if you think about it, since stuff like date and currency formatting is locale-sensitive and shouldn't impact the sorting order). So, how so we effect the sorting order?

To the existing formula, at the end, add "+ 0". This will work for any of the numeric-based output formats (number, currency, or date, since dates are stored internally as numeric offsets from an epoch anyway). So, a formula such as:

=IF(ISBLANK([Due Date]),"12/31/2099",[Due Date])

becomes

=IF(ISBLANK([Due Date]),"12/31/2099",[Due Date])+0