Time Format Calculations

D

dhaliwam

Hi,

I have a column in Excel which contains Time entries. Its using Custo
format of "h:mm".

The problem is that if I want to have sum of entries in this coulmn,
am getting a bizare number?


Is there any way to fix this issue?

Thanks
 
B

Bob Phillips

Format the sum as

[h]:mm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

dhaliwam

Thanks, but its not working.

Its OK if I add two cells but if I try to add more cells again its not
working?

e.g. First two cells contains 9:57 & 9:59, If I add these two cells, it
gives me 19:56 which is correct. Then If I try to add another cell
which contains 11:35, its gives me 7:31 which is incorrect.

Any ideas?

Thanks Again!
 
P

Paul

dhaliwam said:
Thanks, but its not working.

Its OK if I add two cells but if I try to add more cells again its not
working?

e.g. First two cells contains 9:57 & 9:59, If I add these two cells, it
gives me 19:56 which is correct. Then If I try to add another cell
which contains 11:35, its gives me 7:31 which is incorrect.

Any ideas?

Thanks Again!
Click to expand...

Format the cells as [h]:mm or [hh]:mm
The square brackets tell Excel not to wrap at 24 hours.
 
D

dhaliwam

I already tried but I didn't work for me. I am not sure whats wrong?

Any other idea?

Thanks
 
P

Peo Sjoblom

No, you have not tried it correctly. If you format the total cells as
[hh]:mm
it will roll over the hours after 24:00. If I sum all the values from the
earlier post I get
07:31, now if I select that cell and do format>cells>number>custom and type
[hh]:mm
I get 31:31

There is no other way or any other idea unless you want to format is a
decimals

=SUM(A1:A30)*24

format as general will return

31.5166666666667
 
D

dhaliwam

It will work only if those cells are regular cells I mean they values
are not based on calculations, in my case those cells are based on
other cells e.g. =24-(B2-C2). I am getting the sum as 1759:33
even though I am using the [hh]:mm format.


If I add other cells which are not based on calculations, I am getting
the sum as 31:31 which is correct.

Is there any way to fix the sum for calcalated cells?


Thanks!
 
P

Peo Sjoblom

Assuming you are using time format in B2 and C2 then you can't use 24 like
that
If you want to subtract from 24 hours use

="24:00"-(B2-C2)


=24-(B2-C2)

is the same as 24 days i.e. 24*24 =576 hours
 
Top