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

Improve this question

1 Answer 1

Reset to default
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

Improve this answer
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.
    – Mohamed El-Qassas MVP ♦
    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")
    – user1063287
    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.