Time Division

B

bglodde

I've searched everywhere for a solution to these time problems.

1) In a column are a series of times (>100) that are in the hundreds of
hours (time formatted [hh]:mm:ss), I -cannot- SUM this column, so I had
to write a custom function for it. Other columns with lesser time
values SUM() properly.

Sample of the data:
244:11:30
224:58:13
112:24:10

...etc...

Why does SUM not work with large time values?


2) I also need to divide that large time value by a whole number, eg
=21519:51:56/1885

I cannot figure out how to make this happen!

TIA.
 
B

Bob Phillips

SUM does work. It is probably due to the fact that it adds up to more than
24 hours. Sum the cells, and format the result as [h]:mm.ss

--

HTH

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

bglodde

Yes, the columns are properly formatted and the SUM produces: 01:10:1
(formatted [hh]:mm:ss). If I add more "h"'s, then it just prepend
zeros. My custom function produces: 21519:51:56, which is the correc
value.

I should add this data is pasted from another Excel sheet which i
created from an exported report from Access using TransferText
 
J

JE McGimpsey

1) Most probably your "times" are entered as Text, which can often
happen when data is pasted. To coerce to numbers, copy an empty cell,
then select your "times" and choose Edit/Paste Special, selecting the
Values and Add radio buttons. You may need to reformat after the paste.

2) You can directly enter only times up to "9999:59:59", so if your
21519:51:56 is the result of a calculation in another cell, use

=A1/1885

If you need to enter the large time directly, use the fact that XL
stores times as fractional days:

=(21519/24+"00:51:56")/1885
 

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

Similar Threads


Top