Exel help.

botheredbothered Manchester UK
edited April 2004 in Science & Tech
I want to find the differance between two times, say,5:00am to 3:00pm and get the answer 10. I've tried the excel help, even copied their example and just get #value or other errors. Anybody know a formula where I could enter times as above into two cells and get the answer in a third cell? The times could be anywhere in 24 hours.
Cheers guys. :confused:

Comments

  • a2jfreaka2jfreak Houston, TX Member
    edited April 2004
    Make sure all three cells are set as TIME rather than currency or numeric or something else.

    A1 = "5:00 PM"
    B1 = "2:00 AM"
    C3 = "=(A1 - B1)"

    If all cells are configured as TIME (select the cells, then right click and configure) Excel should accurately calculate the time difference.
  • TheSmJTheSmJ
    edited April 2004
    might be easier to change the times into army time, then find the diff, and THEN put them back into civ. time.
  • botheredbothered Manchester UK
    edited April 2004
    a2jfreak, brilliant. I spent a couple of hours messing with this last night and you gave ne the answer in no time. One other thing though, if the first time is say 11:00pm and the second time goes into the next day, say 6:00 am, it gives the wrong answer. How can I get it around that?
  • a2jfreaka2jfreak Houston, TX Member
    edited April 2004
    I don't know. I'll work on it after church tomorrow if you haven't gotten it to work by then.
  • botheredbothered Manchester UK
    edited April 2004
    Thanks aj. I'll have a play with it.
  • botheredbothered Manchester UK
    edited April 2004
    Sorted :clap:
    Say D5 is the start time and C5 is the finish time, the differance in hours is-TEXT(D5-C5+(D5<C5),"h:mm")

    It's the +(D5<C5) that lets it go past midnight.

    Thanks aj,
  • botheredbothered Manchester UK
    edited April 2004
    I've edited the above post 6 times, it won't let me say- it's the +(D5<C5) that lets it work past midnight.


    edit, again. right it's, plus sign, start bracket, D5, less than, C5, end bracket.
    it must be the 'less than' that won't post!
  • botheredbothered Manchester UK
    edited April 2004
    I hope you can understand the above mess. When I try to post the message with a 'less than' symbol in it, it wipes the end of it out.
  • a2jfreaka2jfreak Houston, TX Member
    edited April 2004
    Sorry my reply is so tardy. I've just now gotten home. Pleased to see you've gotten it working, bothered.
  • botheredbothered Manchester UK
    edited April 2004
    I don't even know what tardy means aj, as far as I'm concerned you were a great help, put me on the right lines.
    Thanks again.
Sign In or Register to comment.