Calculating Time

[Meenie]

I have Excel 2003
I have a spreadsheet (I didn't create this spreadsheet and it has much info
filled in) that has a column showing the start date, next column shows the
start time in military time, ,next column shows the end date, next column
shows the end time in military time.
I need a column to show how many hours/mins that equals. IE if column G row
3 = 5/28/2008; Column H row 3 = 1000; Column I row 3 = 5/29/2008; Column J
row 3 = 900; that would be an elapsed time of 23 hours.
How can I make a formula to make it figure that elapsed time ?
Thanks, Meenie

 

[Glenn]

I have Excel 2003
I have a spreadsheet (I didn't create this spreadsheet and it has much info
filled in) that has a column showing the start date, next column shows the
start time in military time, ,next column shows the end date, next column
shows the end time in military time.
I need a column to show how many hours/mins that equals. IE if column G row
3 = 5/28/2008; Column H row 3 = 1000; Column I row 3 = 5/29/2008; Column J
row 3 = 900; that would be an elapsed time of 23 hours.
How can I make a formula to make it figure that elapsed time ?
Thanks, Meenie

=(I3-G3)+((J3-H3)/2400)

The result will be 0.95833, so format the cell as time to get 23:00.


If you want the result to be the number 23,

=((I3-G3)+((J3-H3)/2400))*24

 

[David Biddulph]

But of course doing it that way, 0030 won't show up as half an hour, but as
0.3 of an hour, and 0050 will be treated as half an hour. You'd be better
off using 09:00 for your time, rather than 0900. If you use 0900, you'll
need a more complicated formula to separate out the hours and minutes.

 

[Glenn]

But of course doing it that way, 0030 won't show up as half an hour, but as
0.3 of an hour, and 0050 will be treated as half an hour. You'd be better
off using 09:00 for your time, rather than 0900. If you use 0900, you'll
need a more complicated formula to separate out the hours and minutes.

Good point. This should work with "0900", but may not be "optimized":

=(I3-G3)+((LEFT(RIGHT("000"&J3,4),2)-LEFT(RIGHT("000"&H3,4),2))+(RIGHT(J3,2)/60-RIGHT(H3,2)/60))/24

 

[Meenie]

Hmm didn't work. It brought back a number, but it wasn't the correct number.
It didn't seem to see the days.

 

[Glenn]

Hmm didn't work. It brought back a number, but it wasn't the correct number.
It didn't seem to see the days.

Did you try the revised version, from elsewhere in this thread?

=(I3-G3)+((LEFT(RIGHT("000"&J3,4),2)-LEFT(RIGHT("000"&H3,4),2))+(RIGHT(J3,2)/60-RIGHT(H3,2)/60))/24