Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding Time in Excel - Frustating experience!!
Hi,
I have the following info in an excel spreadsheet: Starttime EndTime Hours SubTotal 8:00 am 9:00 am 1:00 12:30 pm 2:30 pm 2:00 3:00 I am adding all the cells in the Subtotal Column and my grand total is 24 hours...something like 27:30. I used the formula =sum(D1:D34) on the Grand total field on Column D and formatted the cell as [hh]:m following the advice in this discussion forum. My total is still not coming to 27:30. It shows up as 314:30. What am I doing wrong? Can someone help me please? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding Time in Excel - Frustating experience!!
Simply change the format on your grand total member to Time and the one that
shows 37:30:55 (7th in list). This will return: 8:00 AM 9:00 PM 13:00 12:30 AM 2:30 PM 14:00 27:00:00 "vrk1" wrote: Hi, I have the following info in an excel spreadsheet: Starttime EndTime Hours SubTotal 8:00 am 9:00 am 1:00 12:30 pm 2:30 pm 2:00 3:00 I am adding all the cells in the Subtotal Column and my grand total is 24 hours...something like 27:30. I used the formula =sum(D1:D34) on the Grand total field on Column D and formatted the cell as [hh]:m following the advice in this discussion forum. My total is still not coming to 27:30. It shows up as 314:30. What am I doing wrong? Can someone help me please? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding Time in Excel - Frustating experience!!
This returns the value 314:30:00 for my Grand Total.
Digging deeper into my problem, this is what I found: The Hours column has the formula: =B1-A1 etc., Specific Cells on Subtotal column have the formula: =Sum(C1:C3) with Format Time 13:30 The last cell on the Subtotal Column has the formula: =Sum(C1:C33) with Format [hh]:m I introduced values in a new column (Col E) where the values in this column = Col D. Now when I sum Col D into Grand Total with Format [hh]:m it totals up correctly to 26:30. How would you fix this without using Column M? "BobT" wrote: Simply change the format on your grand total member to Time and the one that shows 37:30:55 (7th in list). This will return: 8:00 AM 9:00 PM 13:00 12:30 AM 2:30 PM 14:00 27:00:00 "vrk1" wrote: Hi, I have the following info in an excel spreadsheet: Starttime EndTime Hours SubTotal 8:00 am 9:00 am 1:00 12:30 pm 2:30 pm 2:00 3:00 I am adding all the cells in the Subtotal Column and my grand total is 24 hours...something like 27:30. I used the formula =sum(D1:D34) on the Grand total field on Column D and formatted the cell as [hh]:m following the advice in this discussion forum. My total is still not coming to 27:30. It shows up as 314:30. What am I doing wrong? Can someone help me please? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding Time in Excel - Frustating experience!!
Suppose you have start time in A and end time in B
Col c = b1-a1 (with format [h]:mm) Always maintain the format [h]:mm for differences, totals etc; -- If this post helps click Yes --------------- Jacob Skaria "vrk1" wrote: This returns the value 314:30:00 for my Grand Total. Digging deeper into my problem, this is what I found: The Hours column has the formula: =B1-A1 etc., Specific Cells on Subtotal column have the formula: =Sum(C1:C3) with Format Time 13:30 The last cell on the Subtotal Column has the formula: =Sum(C1:C33) with Format [hh]:m I introduced values in a new column (Col E) where the values in this column = Col D. Now when I sum Col D into Grand Total with Format [hh]:m it totals up correctly to 26:30. How would you fix this without using Column M? "BobT" wrote: Simply change the format on your grand total member to Time and the one that shows 37:30:55 (7th in list). This will return: 8:00 AM 9:00 PM 13:00 12:30 AM 2:30 PM 14:00 27:00:00 "vrk1" wrote: Hi, I have the following info in an excel spreadsheet: Starttime EndTime Hours SubTotal 8:00 am 9:00 am 1:00 12:30 pm 2:30 pm 2:00 3:00 I am adding all the cells in the Subtotal Column and my grand total is 24 hours...something like 27:30. I used the formula =sum(D1:D34) on the Grand total field on Column D and formatted the cell as [hh]:m following the advice in this discussion forum. My total is still not coming to 27:30. It shows up as 314:30. What am I doing wrong? Can someone help me please? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding Time in Excel - Frustating experience!!
Format ALL your time cells as [hh]:mm, not only the grand total, and you
will hopefully spot the problem. HTH. Best wishes Harald "vrk1" wrote in message ... Hi, I have the following info in an excel spreadsheet: Starttime EndTime Hours SubTotal 8:00 am 9:00 am 1:00 12:30 pm 2:30 pm 2:00 3:00 I am adding all the cells in the Subtotal Column and my grand total is 24 hours...something like 27:30. I used the formula =sum(D1:D34) on the Grand total field on Column D and formatted the cell as [hh]:m following the advice in this discussion forum. My total is still not coming to 27:30. It shows up as 314:30. What am I doing wrong? Can someone help me please? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding Time in Excel - Frustating experience!!
Harold - You were spot on the issue. One of the cells was not in this format
and this was causing the issue. Many thanks to everyone that volunteered their time and expertise for me. Much appreciated! "Harald Staff" wrote: Format ALL your time cells as [hh]:mm, not only the grand total, and you will hopefully spot the problem. HTH. Best wishes Harald "vrk1" wrote in message ... Hi, I have the following info in an excel spreadsheet: Starttime EndTime Hours SubTotal 8:00 am 9:00 am 1:00 12:30 pm 2:30 pm 2:00 3:00 I am adding all the cells in the Subtotal Column and my grand total is 24 hours...something like 27:30. I used the formula =sum(D1:D34) on the Grand total field on Column D and formatted the cell as [hh]:m following the advice in this discussion forum. My total is still not coming to 27:30. It shows up as 314:30. What am I doing wrong? Can someone help me please? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Does anyone have any experience with the Forms Toolbar? | Excel Discussion (Misc queries) | |||
Looking for experience with counting and conditions in excel 2003 | Excel Worksheet Functions | |||
Convert excel spreadsheet application to a java or .Net Experience | Excel Discussion (Misc queries) | |||
Experience with VERY large worksheets | Excel Discussion (Misc queries) | |||
Excel & 'Thin Client' experience anyone ? | Excel Discussion (Misc queries) |