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
    Silver Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,609
    Thanks
    45
    Thanked 77 Times in 72 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 14: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
    3,444
    Thanks
    166
    Thanked 651 Times in 619 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
    3,444
    Thanks
    166
    Thanked 651 Times in 619 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
  •