5

How Can I achieve displaying time difference between two type "date and time " columns? I want to put the time difference in another column type "based upon calculation" of those two. I want to display difference of time between column "created" and column "closed"

EXAMPLE:

Start Time 1/24/2009 7:00 AM End Time 1/24/2009 3:00 PM Duration 8 hours

1 Answer 1

13

Try the following formula

=TEXT(Created-Closed,"h")

enter image description here

[Output]

enter image description here


In case you need to show the exactly Hours and minutes, try this

=TEXT(Created-Closed,"hh:mm")

[Output]

enter image description here


Regarding Days and Hours and Minutes, try the below one

=DATEDIF(Created,Closed,"d") &" days .."& TEXT(Created-Closed,"hh:mm")

[Output]

enter image description here

4
  • Thank you very much, one more question. If I want to display it in days hours and minutes? Thanks
    – Lubos
    Aug 9, 2017 at 12:26
  • 3
    You are welcome, please check the new formula for your new request. Aug 9, 2017 at 12:49
  • 2
    Very useful answers!
    – Lubos
    Aug 9, 2017 at 13:27
  • also, this doesn't seem to handle negative hours, for example if 'Start` equals 08/06/2020 08:00am and Finish equals 08/06/2020 12:00am and this formula is used =TEXT(Finish-Start,"hh:mm"), it returns 08:00 ... reversing the values in the formula returns the same result, ie =TEXT(Start-Finish,"hh:mm") Jun 8, 2020 at 23:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.