Adding Time in Excel - Frustating experience!!

[vrk1]

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(D134) 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?

 

[BobT]

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]

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?

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

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(D134) 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?

 

[Jacob Skaria]

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

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?

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

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(D134) 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?

 

[Harald Staff]

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]

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!

Format ALL your time cells as [hh]:mm, not only the grand total, and you
will hopefully spot the problem.

HTH. Best wishes Harald

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(D134) 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?