A customer recently contacted me with an interesting question.
They were using a SharePoint task list to help schedule jobs for field engineers but rather than have a Start Date and End Date field they wanted to have a Start Date and Duration field and automatically work out the End Date.
So End Date = Start Date + Duration
They were then planning to display this using Planner in the By Category view which is idea for resource booking as it makes it easy to see when a resource is booked or free (SharePoints built in gantt chart can’t do this swimlane style of view and doesn’t support using calculated columns).
Now this is easy to do if the Duration is specified in days – in fact you just use the equation above – but what if Duration is in hours or minutes?
First port of call is this page from Microsoft showing examples of common date time formula you can use in SharePoint calculated columns hmmm, adding days OK… adding months Check… adding years OK too….but nothing about hours. Back to the drawing board.
The key bit of knowledge is that SharePoint stores all date/time values internally as the number of days since Jan 1st, 1900. So the number 100.5 in a date/time field is 100 days and 12 hours from 1st Jan 1900 at 00:00
|Free SharePoint Calculated Column Cheat Sheet|
|a handy 3 page reference for calculated column functions and formulas.|