0

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.

Improve this question
3
  • =Text([End Date and Time]-[Start Date and Time],"d hh:mm")
    – Marko Tica
    Jun 30, 2017 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, 2017 at 0:03
  • Thanks for this info folks! I know this is probably unreasonable...but... is there any chance there's a way to subtract non-working hours? We're trying to track how long something took to complete...but... the time shouldn't count if (for example) an item was added to the list at the end of the work day, then finished during the first hour of the next work day... etc. I know, looking for the genie in the bottle....
    – user88158
    Dec 20, 2019 at 11:54

1 Answer 1

Reset to default
5

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

Improve this answer
2
  • 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, 2017 at 0:04
  • Good to know it worked. please do accept the answer and up vote :)
    – SharePointer
    Jul 3, 2017 at 9:19

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