Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Swift Current, Sask. Canada, Saskatchewan, Canada
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create time difference formula (Excel 2000)

    I am a novice to Excel. I am making a time sheet. I have been trying to make a formula to subtract start time from stop time, but it always gives me a total based on 100 instead of 60. 12:00-8:30 gives me 3.7 instead of 3.5. Can someone help me?

  2. #2
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create time difference formula (Excel 2000)

    Hi S Peters,

    Format A1, A2 and A3 as Time.
    Type in A1: 12:00
    Type in A2: 8:30
    Type in A3: +A1-A2

    A3 will show you 3:30

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create time difference formula (Excel 2000)

    If you are getting 3.7 when you do that subtraction, then what you have in those cells are the numbers 12.0 and 8.3, not the times 12:00 and 8:30. What you need to do is enter the times exactly as you typed them in your message with the colon between the hour and the minute and a two digit minute. It looks like the times were entered with decimal points, not a colon between the hour and the minute.

    Next, how you do the subtraction and how you format that cell depends on how you want the result to be displayed. If you want the result to display as 3:30 (three hours and thirty minutes), then enter the formula =A2-A1 (if the start time is in A1 and the end time is in A2) and format the cell with the custom format [h]:mm with the brackets around the h. That format is elapsed time and will allow times greater than 24 hours to display correctly.

    If you want the result to display as 3.50, then enter the formula =(A2-A1)*24 and format the cell as a number with two decimal places. This is because Excel stores times as the fraction of a day.
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Swift Current, Sask. Canada, Saskatchewan, Canada
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create time difference formula (Excel 2000)

    I have been playing with this formula using the .50 hours instead of the 30 minutes, and works great. The only thing is if I use 09:15 or 09:45, the answer does not come out right. Maybe I will have to stick to half hours. Thanks for the help.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create time difference formula (Excel 2000)

    As Legare wrote, you *must* enter the two times with colons between hours and minutes: 9:15, not 9.15. If you enter times as 9.15, you'll have to do complicated arithmetic to get the difference between times right. If you enter them as 9:15, Excel will do most of the work for you. See attached workbook.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create time difference formula (Excel 2000)

    If you are entering the times like you typed in your message (with the colon between the hour and the minute), there should be no problem. Can you attach a workbook that shows the problem you are having?
    Legare Coleman

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Swift Current, Sask. Canada, Saskatchewan, Canada
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create time difference formula (Excel 2000)

    Thanks so much for all the help people. I was having some troubles with all these suggestions until I found the right formatting for the particular cells. Was also having trouble converting the accumulated times into earnings until I tried using the *24 thing with the right formatting in the cells. Again Thank You all. You are fantastic.

Posting Permissions

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