Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    elapsed time - to next day (Excel 2000)

    I am attaching a spreadsheet for anyone who could help me fix this formula so that it will see in hours and minutes that I have in fact went to the next day, and have it so it can be autofilled across the column. All I can do up to this point is the 1-(b17+b16) but thats not working...especially since when I autofill I don't always want to add a day to the formula, because a day doesn't always pass by before the next time.
    Thanks Nannette
    Attached Files Attached Files
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: elapsed time - to next day (Excel 2000)

    Excel keeps times as a fraction of a twenty-four hour day, so that it can keep dates and times as one number: the date in the whole number portion and the time in the fractional portion.

    So, to compute the delta time in hours between B17 and B16
    <pre>=TRUNC((B$17-B$16)*24)</pre>

    or in minutes
    <pre>=TRUNC((B$17-B$16)*24*60)</pre>


    Note I used column absolute references so that you could copy them. Is this what you wanted? I had trouble understanding your spreadsheet and post. If not, please try again and maybe put the actural numbers that you expect to see in the sheet. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: elapsed time - to next day (Excel 2000)

    I tried your TRUNC formula, but did not get the results I expected...I am resending this sheet with the answers I expect to receive...24 for hours and 15 for the minutes...which needs to be autofilled across the columns, so whatever the formula...it needs to be adjustable for less then 24 hours and more then 24 hours. Thanks
    Attached Files Attached Files
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: elapsed time - to next day (Excel 2000)

    OK, I think I understand the 24 hrs now: if B16=11:00 PM and B17=1:00 AM, then you want B19 to be 2 hours. Correct? If so, try<pre>=IF(B17>B16,HOUR(B17-B16),HOUR(1+B17-B16))</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: elapsed time - to next day (Excel 2000)

    You are an absolute genius...thank you so much for your valuable time. I hope that I can pop in and help out sometime and repay the favor, by being helpful to someone else...
    Bless you,
    Nannette <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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