# Thread: elapsed time - to next day (Excel 2000)

1. ## 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

2. ## 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

3. ## 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

4. ## 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>

5. ## 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>

#### Posting Permissions

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