How to calculate the total time difference between two date time fields in a custom list

Saw this asked in a forum, and knew I’d need the answer some day… so figured it was worth storing here amongst the litter.

If you need to calculate the TOTAL time difference between two date time fields in a custom list, in SharePoint (such as the example start and end times below):

Start time: 11/6/2008 1:00 PM
End Time: 11/7/2008 8:23 AM

Total Time in Office: 19:23 (for this example, we want to determine how long someone has been in the office)

And the answer please?

If you want to display exactly like 19:23″, you have to calculate to a text type (StartTime and EndTime are the date/time fields you indicated above):

Either

   1: =CONCATENATE(TEXT(HOUR([EndTime]-[StartTime]),"#"),":",TEXT(MINUTE([EndTime]-[StartTime]), "#")) 

Or

   1: =CONCATENATE(TEXT(HOUR([EndTime]-[StartTime]),"#")," hours ",TEXT(MINUTE([EndTime]-[StartTime]), "#"), " minutes")

If you want to compare with other values, better to keep it numeric – then it is very simple:

   1: =[EndTime]-[StartTime]

To serve your purpose, it can be like this:

   1: =IF([EndTime]-[StartTime] > 1, 0, ([EndTime]-[StartTime])*24)

This results in range from 0 to 24 (reset to 0 when it is greater than 24).

Or if you want, you can also combine the above:

   1: =IF([EndTime]-[StartTime] > 1, "Greater than 24 hours", CONCATENATE(TEXT(HOUR([EndTime]-[StartTime]),"#")," hours ",TEXT(MINUTE([EndTime]-[StartTime]), "#"), " minutes")) 

Reference: http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/MS-SharePoint/Q_23887580.html (and THman)