# How to calculate Days, Hours and minutes between two different dates and times

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.

• =Text([End Date and Time]-[Start Date and Time],"d hh:mm") 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.... Dec 20, 2019 at 11:54

Try this:

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

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