SharePoint Calculated Columns - adding hours onto a date field.

Is this your first time here? Get regular updates

SharePoint Calculated columns – Adding hours onto a date field

Date:May 18th, 2010 Author:Ryan Tags: Calculated Columns, Formula, SharePoint, Tip
Category: General, SharePoint Ideas Comments:1 ;

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

Armed with this we get the following formula (where Duration is in hours)

=[Start Date]+([Duration]/24)

So what’s happening here?

([Duration]/24) turns the number of hours in Duration to fractions of a day (e.g. 1 hr is 0.0416 of a day)

Then we add that number to the Start Date and finally as our calculated column is defined as a date/time field SharePoint is turning the number (e.g. 40,296.50) back into a readable date/time value.

Job done!

Tips

This will work with Durations greater than 24 or even 1/2 hours etc.
If you want to specify Duration in minutes then instead of /24 above you would use /1440 (the number of minutes in a day).
You can use this for anything that has a duration – tasks, service jobs, meetings, flights, equipment booking etc.

Further reading