Adding Time in Excel - Frustating experience!!

Adding Time in Excel - Frustating experience!!

Post by dnJrM » Sun, 03 May 2009 05:53:01


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!!

Post by Qm9iV » Sun, 03 May 2009 06:23:01

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

 
 
 

Adding Time in Excel - Frustating experience!!

Post by dnJrM » Sun, 03 May 2009 06:38:01

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?
 
 
 

Adding Time in Excel - Frustating experience!!

Post by SmFjb2IgU2 » Sun, 03 May 2009 13:19:01

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
 
 
 

Adding Time in Excel - Frustating experience!!

Post by Harald Sta » Sun, 03 May 2009 18:48:07

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

HTH. Best wishes Harald
 
 
 

Adding Time in Excel - Frustating experience!!

Post by dnJrM » Sun, 03 May 2009 22:48:01

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!