M
maryc
Want to check my phone bill and need simple (please) instructions of how to
calculate total figure. Thanks
calculate total figure. Thanks
B
Bob Phillips
=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)
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)
M
maryc
Thanks - have done as you suggested but end up with 0:00:00 - help!
--
maryc
--
maryc
Bob Phillips said:
S
Sandy Mann
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
[email protected]
[email protected] with @tiscali.co.uk
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
[email protected]
[email protected] with @tiscali.co.uk
maryc said:
B
Bob Phillips
or do it in trimming and summing in one
=SUM(IF(B1:B10<>"",--(TRIM(SUBSTITUTE(B1:B10,CHAR(160),"")))))
as an array formula
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
=SUM(IF(B1:B10<>"",--(TRIM(SUBSTITUTE(B1:B10,CHAR(160),"")))))
as an array formula
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
Sandy Mann said:
S
Sandy Mann
Bob Phillips said:
That's what I like about these NG's - joined up thinking! <g>
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
[email protected]
[email protected] with @tiscali.co.uk
B
Bob Phillips
You're sounding like a politician Sandy <bg>
Bob
Bob
M
maryc
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
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 said:
S
Sandy Mann
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
[email protected]
[email protected] with @tiscali.co.uk
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 entertoday'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
[email protected]
[email protected] with @tiscali.co.uk
maryc said:
M
maryc
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
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 said: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 (
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
[email protected]
[email protected] with @tiscali.co.uk
maryc said: