SharePoint 2010 计算时间间隔

Date Time duration using Calculated Field column

March 2, 2012 Kelv Leave a comment Go to comments

 

Calculated Field column can be use effectively to get the differences or duration between two date time. There is a list of useful formula in MSDN: Calculated Field Formulas. But some of the formula is actually quite buggy and not providing the accurate result. Another difficult part is to get the result in the preferred format.

Just to share some of the formula I modified recently to get accurate calculation and in my desired format.

Format: d hh mm (Day Hour Minutes)

Formula:
=DATEDIF([Start Time],[End Time]-(MOD([Start Time],1)>MOD([End Time],1)),”d”)&” Day(s) “&TEXT(MOD([End Time]-[Start Time],1),”hh”" Hour(s) “”mm”" Min(s)”"”)

Format: hh mm (Hour and Minutes)

Formula:
=(DATEDIF([Start Time],[End Time],”d”)*24+HOUR([End Time]-[Start Time]))&” Hours(s) “&MINUTE([End Time]-[Start Time])&” Min(s)”