# How can i find the days, hours and minutes difference between two columns in a calculated column

I want to create the functionality of this formula which works in excel =Text([end date]-[start date],"d:h:mm") in a sharepoint calculated column. It works with h:mm, but when I include the d or D I get unexpected results.

• After more consideration the 'd' doesn't do what I was intending. When the dates differ by several months it gives unexpected results. – Robert Feb 3 '14 at 19:43
• What would you want to see when the difference is several months? – teylyn Feb 3 '14 at 19:45
• I want the result as number of days : number of leftover hours : number of leftover minutes – Robert Feb 3 '14 at 21:49
• See my latest edit. – teylyn Feb 3 '14 at 23:19
• THANK YOU!! that works perfect Teylyn. If only date time columns would use one minute increments I would be completely happy. – Robert Feb 4 '14 at 16:51

You cannot connect the "d" with a colon to the time value. The colon can only be used to separate hours, minutes and seconds. Use a blank instead of the colon

=Text([end date]-[start date],"d h:mm")


Edit: If the days go over one month, then the following approach may be better:

=IF(End-Start>1,INT(End-Start)&":","")&TEXT(End-Start,"h:mm")


Days will only show if present and can run into the tens of thousands. Note: My regional settings are DMY.

You can also try formats in this post to see if you get the result you want http://panvega.wordpress.com/2008/12/08/ddwrtformatdate-with-different-formatflags/

Saw another example this might work too

Text([end date]-[start date],"d") &":"& Text([end date]-[start date],"h:mm")

• Thanks Teylyn, couldn't up vote your answer or attach my comment, too new on this forum :) – Wonderboy Jan 30 '14 at 21:56

CONCATENATE(ROUNDDOWN([end date]-[start date],0),":",TEXT([end date]-[start date],"hh:MM")