| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating Regular time, overtime and double time | Excel Worksheet Functions | |||
| Calculating time and negative time | Excel Worksheet Functions | |||
| calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions | |||
| Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
| Calculating effective time from start/end date+time | Excel Worksheet Functions | |||
Linear Mode
