RSS Feed Is this your first time here? Get regular updates

SharePoint Calculated columns – Adding hours onto a date field

Date:May 18th, 2010 Author: Tags: , , ,
Category: General, SharePoint Ideas Comments:7 ;

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

Free SharePoint Calculated Column Cheat Sheet
a handy 3 page reference for calculated column functions and formulas.

Tags: , , ,

Did you enjoy this post? Get the next one by RSS or by email

7 Responses to “SharePoint Calculated columns – Adding hours onto a date field”

  1. Thanks Ryan. Can never have enough calculated formulas we can just copy and paste, keep ‘em coming.

  2. Bill Ponzini says:

    Hello Ryan

    We were using your above examples (see specific code below) and everthing was working great. We have come back to a new year, 2011 and all of a sudden we are getting ?NAME error in the end: field (field which added 30 mins on).
    The year change may be a ‘red herring’, is there a system setting/change which could affect this solution?
    Incidentally the same calculation still works ok in Excel?

    Thanks
    Bill

    Fields:
    Start date: (date and time input) 17/01/2011

    Slot: (limited choice input) ‘Appointment Slot 1 – 09:30′

    Start: (calculated date and time – =TEXT([Start Date:],”dd/mm/yyyy”)&RIGHT([Appointment Slot],6) 17/01/2011 09:30

    End: (calculated date and time – =[Start]+(1/48) was returning 17/01/2011 10:00 now ?NAME

  3. Effie says:

    Great Post!
    I’ve used your sample but for minutes:
    Duration/1440
    Working perfect!

  4. Evgenich says:

    Very-very-very good tip!!! thank you!

  5. Dan says:

    Bonus question: now that you have a calculated column that is date/time – can you create a “Calendar” view using it?
    (I keep getting “Unexpected error” – (even though resulting column values are type “date & time” and have normal values…)

    (Just me?)

  6. Anand says:

    in SharePoint date and time how to increase the minute 1,2,3 like that bcas .The default Datetimecontrol of SharePoint is increment of 5 min. how can decrease or increase?

Leave a Reply

Anti-Spam Quiz:

We need to confirm your email address so we have just sent you an email.


Please click the link in the email and you will have immediate access to the download