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.
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:
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")