Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Need formula to calculate time duration from 12:45 to 1:00

    Hi all,

    I need a formula to calculate time duration between 12:45 to 1:00.

    The answer of course should be 0:15 i.e., 15 minutes

    12:45 is 45 mins after midday and 1:00 is 1 c'clock lunch time

    E.g., 12:45 is in cell A1, and 1:00 is in cell B1,
    and I want the duration in cell C1 , what formula do I put in cell C1?

    TIA

    -avi

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Try: =TEXT(B1-A1,"h:mm")
    with A1 being 12:45 PM and B1 being 1:00 PM

    or: =MINUTE(B1-A1)

    or, if the difference is in excess of 60 minutes: =(B1-A1)*1440
    Last edited by kweaver; 2012-05-03 at 15:51.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts
    None of them seem to work.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    kweaver is correct, but..
    in cell [B1], for 1:00pm, you need to enter this in 24-hr clock format i.e. as 13:00.
    You can still format the cell to show 13:00 as 1:00 PM, but the contents are 13:00
    see attached file

    zeddy
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts
    >> in cell [B1], for 1:00pm, you need to enter this in 24-hr clock format i.e. as 13:00.

    OK, if I do that then the following solution works:

    TEXT(B1-A1,"h:mm")

    The other two just display 12:00 AM.
    ----------

    But what if I don't want the time values in A1 and B1 to be displayed with "AM" and "PM"?

    I.e., which ever that the user types in the time, the calculation formula in C1 should handle it well and display the right duration.

    -avraham



  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Excel stores time as a decimal number, as 'part' of a day.
    6:00am = 0.25
    midday = 0.5
    9:00pm = 0.75 etc.

    So, to work with a '12-hour' clock only, and always assuming that the time entered in [B1] is always after the time entered in cell [A1], you just need to use the following formula in cell [C1]:
    =TEXT(IF(B1<A1,B1+0.5-A1,B1-A1),"h:mm")

    You can use a custom format of h:mm if you don't want to see am/pm.

    see attached file

    zeddy
    Attached Files Attached Files

  7. The Following User Says Thank You to zeddy For This Useful Post:

    amakeler (2012-05-06)

  8. #7
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Yes! That's it!

    Thanks!

    -avraham

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •