Results 1 to 9 of 9

Thread: Calculate Time Difference Between Time In HH:MM

  1. #1
    Junior Member
    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0

    Calculate Time Difference Between Time In HH:MM

    Morning all,

    Range A4:A9 contains times (14:00-15:00, 15:00-16:00, 16:00-17:00, 17:00-18:00, 18:00-18:30, 19:00-20:00)

    in range B4:B9 i have - (=C4, =C5, =C6, =C7, =C8/2, =C9)

    the cell with C8/2 is due to the fact that in Cell A8 the time is only half an hour, the rest are a full hour.

    I want to make the formulas in B4:B9 more felxible, as i modify the sheet to fit its purpose, the times in A4:A9 may not always be the same (but will alwyas be either a full hour or a half hour)

    The intention is to have a formula in B4:B9 that will see if the times difference is a full hour or a half, if its a full hour then just show me the figure from C, if it is a half hour, show me half of the figure in C.
    I thought i could do it by -
    Code:
    =IF(LEFT(A4,2)-RIGHT(A4,2)=1,C4,C4/2)
    but that just gives me a figure of 1.
    Which thinking about it was doomed to fail as the RIGHT(A4,2) will only show 2 zeros lol

    Any suggestions please?

    KR

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Not the most elegant one, but did you try

    =IF(ROUND((TIMEVALUE(RIGHT(A5,5))-TIMEVALUE(LEFT(A5,5)))*24,10)=1,C5,C5/2)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Stalker View Post
    Is it elegant? no
    Do i care? no
    Does it work? yes - Thankyou very much indeed!
    Some may consider this "elegant" (I'm not sure what that word actually means as it applies to formulas ), but if not, it is definitely shorter ...

    =24*C4*(RIGHT(A4,5)-LEFT(A4,5))
    Last edited by Rick Rothstein; 03-26-2013 at 10:49 PM.

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Elegant - antonyms: Clunky, Inefficient, Not-the-best-approach (Ref. Excel Fox)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    Inefficient, Not-the-best-approach (Ref. Excel Fox)
    May I ask in what way?

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Rick, I was referring to what I meant by being 'elegant'. For example, mine was clunky, you proved it. Mine was inefficient, you proved it, mine was not the best approach, you proved it.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    Rick, I was referring to what I meant by being 'elegant'. For example, mine was clunky, you proved it. Mine was inefficient, you proved it, mine was not the best approach, you proved it.
    Oh, I thought you were directing those comments at my formula and I was wondering in what way it had fallen short. I thought maybe you were saying there was some kind of hidden timing issue with my code (which your use of TIMEVALUE avoided) that I was not aware of, so I was asking you to clarify that for me.
    Last edited by Rick Rothstein; 03-27-2013 at 09:27 PM.

  9. #9
    Junior Member
    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0
    Well, they both work, so thanks very much for the input!

Similar Threads

  1. Inserting time in spreadsheet
    By papabill in forum Excel Help
    Replies: 17
    Last Post: 10-25-2013, 01:34 PM
  2. Correcting “Negative” Time Difference Calculation in Excel
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  3. Get time difference in excel using vba
    By LalitPandey87 in forum Excel Help
    Replies: 1
    Last Post: 10-09-2012, 07:57 AM
  4. Displayin Date/Time in "original" time zone
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 04-21-2012, 02:02 AM
  5. Spreading a time range (shift time, etc) in columns.
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 1
    Last Post: 08-23-2011, 11:45 AM

Posting Permissions

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