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

12

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 | |
  • Thank you very much, one more question. If I want to display it in days hours and minutes? Thanks – Lubos Aug 9 '17 at 12:26
  • 3
    You are welcome, please check the new formula for your new request. – Mohamed El-Qassas MVP Aug 9 '17 at 12:49
  • 2
    Very useful answers! – Lubos Aug 9 '17 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 at 23:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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