I have a Start Date and Time column and a End Date and Time column, I also have a calculated column using a Single Line of Text formula:

=TEXT([End Date and Time]-[Start Date and Time],"d:hh:mm").

However this produces an error. It works with just hh:mm as long as the two dates are the same date, but does not calculate past a 24hr time period.

So I would like to calculate and output the difference between any two dates in Days, Hours and Minutes. ie. 2 days 4 hours 30 minutes etc.

I don't have access to SharePoint Designer, and using enterprise SP2013.

  • =Text([End Date and Time]-[Start Date and Time],"d hh:mm") – Marko Tica Jun 30 '17 at 7:12
  • This worked up to a point then it didnt calculate correctly with certian times greater than a day. But thank you very much anyway! – Dean Jul 3 '17 at 0:03

Try this:

=CONCATENATE(INT([Date 2]-[Date 1])," Days ",TEXT([Date 2]-[Date 1],"h"" hrs ""m"" mins """))

More information here

if you want only days, month year then this:

=CONCATENATE(DATEDIF([Date 1],[Date 2],"y")," Years,",DATEDIF([Date 1],[Date 2],"ym")," Months,",DATEDIF([Date 1],[Date 2],"md")," Days")

More information here

  • Perfect in everyway! You rock! this worked perfectly and with the addtional text for display makes it a neat solution too. Thank you and appreciated! Regards, Dean – Dean Jul 3 '17 at 0:04
  • Good to know it worked. please do accept the answer and up vote :) – SharePointer Jul 3 '17 at 9:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.