Date Time duration using Calculated Field column

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

4 thoughts on “Date Time duration using Calculated Field column”

  1. Hi Kelv,
    Works great, one issue I have maybe you can help with is if my end time is blank I get #NUM! how do you add if blank display NA ?

    1. Hi Aliyyah,
      This formula is to display the duration in Text. I will suggest your have another calculated column to store the duration(Day or Hour, etc..) in Number. Then only you can use the Sum function in the list.

Leave a Reply

Your email address will not be published. Required fields are marked *