Adding Time in Excel - Frustating experience!!

V

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(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?
 
B

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
 
V

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?

BobT said:
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 said:
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?
 
J

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


vrk1 said:
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 said:
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 said:
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?
 
H

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
 
V

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!

Harald Staff said:
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 said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top