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?

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?

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?

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?

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?

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?