|
Calculating Time
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 |
Calculating Time
Meenie wrote:
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 |
Calculating Time
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. -- David Biddulph "Glenn" wrote in message ... Meenie wrote: 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 |
Calculating Time
...
Meenie wrote: 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 wrote: 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. -- David Biddulph 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 |
Calculating Time
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" wrote: Meenie wrote: 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 |
Calculating Time
Meenie wrote:
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 Meenie wrote: 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 |
| All times are GMT +1. The time now is 04:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com