Hi again

I printed off your instructions and was doing ok but admit I've still lost
the plot! The phone times were downloaded from site automatically into Excel
format. I'm o.k. til you say "so we now have the time in
numbes....highlight etc. etc. Lost it - sorry. Should I be formatting the
column back to general or should I just forget it and add manually?!!!
--
maryc


"Sandy Mann" wrote:

Hi maryc,

I assume that by *Excel format* you mean General format - numbers like
0.0451388888888889

Dates in Excel are just a count of the number of days since 1/1/1900 but
formatted to look like a date. In an empty cell try pressing and holding
the control key while you press the semi-colon key (;) which will enter
today's date. Now re-select that cell and change the formatting to General
and you will see the number 39147 which is a count of the number of days.
Now it is imortant to realise that we have NOT changed what was being held
in the cell - it always was that number even when it looked like a date.

Times are also *dates* 1/1/1900 is the first day so it is the number 1. At
12 pm on 1/1/1900 is half way through the day so it is 0.5 so when we see a
time of 12:00 in a cell it actually holds the number 0.5 Try entering a
time and then changing the format to General.

So we now have the times as numbers we can now past them back into the
column that they were in. If we just copy and paste we will get a circular
reference. So highlight the numbers that the formulas created and copy them,
(right-click Copy), then select the first cell in the original times
column and then Right-Click Paste Special. This will paste the values
over the text values that were there. Having done that highlight the
numbers you just pasted and reformat them as times. You can now add them
up, (with, as Bob said, the SUM() cell custom formatted as [h]:00).

Note however that if you use Bob's fomula:

=SUM(IF(B1:B10<"",--(TRIM(SUBSTITUTE(B1:B10,CHAR(160),"")))))

At the bottom of the original *text times* (with the ranges adjusted to what
your ranges are), and array enter it by pressing and holding the CONTROL and
SHIFT keys pressed while you press the ENTER key it will add the *text
times* up without any using any other column. If you do it right then
Excel will put curly braces aroundthe formula like:

{=SUM(IF(B1:B10<"",--(TRIM(SUBSTITUTE(B1:B10,CHAR(160),"")))))}

But DON'T put them in yourself -let Excel do it

Post back if you require any further help


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"maryc" wrote in message
...
Hi Sandy,

Yes, I did and it went straight to Excel format. Now if you could just
explain in more detail what I need to do cos it went right over my head!
Help please
--
maryc


"Sandy Mann" wrote:

maryc,

Did you download the phone bill from the net? If so it may actually be
text. Try in another column:

=--TRIM(SUBSTITUTE(A1,CHAR(160),""))

Then if it turns it into numbers copy and Paste Special Values the new
data back over the original data and reformat the column as time.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"maryc" wrote in message
...
Thanks - have done as you suggested but end up with 0:00:00 - help!
--
maryc


"Bob Phillips" wrote:

=SUM( B:B)

and format the totals cell as [h]:mm:ss

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"maryc" wrote in message
...
Want to check my phone bill and need simple (please) instructions of
how
to
calculate total figure. Thanks
--
maryc