1

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
3

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

enter image description here

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

enter image description here

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

1

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
-1

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

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.