Time Format Calculations

  • Thread starter Thread starter dhaliwam
  • Start date Start date
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
 
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)
 
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!
 
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.
 
I already tried but I didn't work for me. I am not sure whats wrong?

Any other idea?

Thanks
 
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
 
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!
 
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
 
Back
Top