1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to deduct time values and get results in Hour : Minutes format

Discussion in 'Ask an Excel Question' started by ThrottleWorks, Jun 19, 2017 at 3:16 PM.

  1. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,676
    Hi,

    Please see attached file for details.
    I am trying to deduct time values and get time remaining in hours and minute format.
    Can anyone please help me in this.

    Attached Files:

  2. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,225
    In your current format...

    Something like.
    =IF(D4<E4,1+E4-D4,E4-D4)
    ThrottleWorks likes this.
  3. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,676
    Hi @Chihiro sir, thanks a lot for the help. However, I am still not able to get desired result. Could you please help if you get time.

    Have a nice day ahead. :)

    Attached Files:

  4. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,225
    ? Your results in column A seems to be off for bunch of examples.

    You'll need to explain what date Cut off belongs (today or yesterday).

    And NOW portion, should it not include date portion?

    I'd suggest you use NOW() in NOW column (instead of trying to extract time portion only).

    Then formula in column A becomes...
    =IF(E21>D21,E21-TODAY()+D21,TODAY()+D21-E21)
    ThrottleWorks likes this.
  5. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,046
    upload_2017-6-21_0-29-12.png

    Maybe……….

    1] In "Time remaining / Elapse" A4, formula copy down :

    =TEXT(ABS(B4-C4),IF(B4<C4,"-",)&"[h]:mm:ss")

    2] In "Time remaining" D4, formula copy down :

    =(B4-C4)*1440

    3] Formula Checking (Total min) L4, formula copy down :

    =MID(A4,(LEFT(A4)="-")+1,8)*1440

    4] See attached file.

    Regards
    Bosco

    Attached Files:

  6. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,676
    Hi @Chihiro sir, sorry for late reply. I was out of station yesterday.
    As of now I am not required to consider 'Date' part of the time value.

    My ideal output would as mentioned below.
    70 minutes = 1:10 (one hour 10 minutes).

    Thanks for the help. Have a nice day ahead. :)
  7. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,676
    Hi @bosco_yip sir, thanks a lot for the help. Your formula seems to be working for me. Will revert with details. Have a nice day ahead.:)

Share This Page