Adding durations/phone bill

[Ian]

Hi
I'm trying to add the durations on my phone bill (yes it was a biggie!!).
Tried various things including formatting the cells to custom hh:mm:ss.
High light a cell, click sigma, select range of cells, enter. 00:00:00
everytime.
I'm using excel '97 that comes with office pro '97 or something like that.
The original file was downloaded from Vodafone as an excel and is colon
separated.
Any ideas? Any other aplications I could paste into that would do it?
Cheers me dears
BB

 

[Sandy Mann]

I always suspect that downloaded data contains non-breaking spaces character
160. Try highlighting the data and the selecting Edit > Replace. In Find
What: enter 0160 from the number pad NOT the numbers over the letter keys.
(you will not see anything entered in the dialog box because it is a space.)
Leave the Replace with empty and select Replace all. If that does not
correct the problem then Then do the same but use a space instead of 0160

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ian]

Tried that get :- "MS excel cannot find matching data to replace. No cell in
the selection contains what you typed, or no records match the criteria".
I did see the numerals entered and I'm using Num lock on a laptop....Just in
case that means something.
Cheers me dears.

 

[Sandy Mann]

My apologies, when entering the 0160 press and hold pressed the Alt key. I
am using a laptop as well so it should work if you have any non-breaking
spaces.

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Pete_UK]

I download Vodafone data regularly for two of my clients. The duration
field looks like a time, eg 00:03:40, but it is actually a text value.
You can convert it into a proper time by means of this formula:

=VALUE(F2)

and copy this down. I prefer to work in seconds, so I use this
formula:

=F2*60*60*24

formatted as a number with 0 dp. If you want the total of this to show
in hh:mm:ss format, then just divide the total by 60/60/24.

Hope this helps.

Pete

 

[Ian]

Just took a small sample and over wrote the colons. It works but can't do
that for the whole doc.

 

[Pete_UK]

Just continuing with this, then, a non-formula approach is to
highlight the column of data, then CTRL-H (Find & Replace) -

Find What : (i.e. colon)
Replace With : (also a colon)

Click Replace All.

Hope this helps.

Pete

 

[Sandy Mann]

Hi Pete,

I'm a bit lost here now, I don't know if Ian was replying to you or me. As
you have experience of downloading Vodafone data I will bow out and leave
you to help Ian,

--
Regards,

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ian]

Not saying this is correct, could be me, but trying the alt key method
yealded the same result.
The ctrl H method works.
I only say this in case I'm doing something wrong or it's usful info for the
future.
Cheers me dears

 

[Ian]

Oh god hope I haven't caused offence. All input appreciated!
Tried the alt 0160 same problem. I only mention this to keep "the across
this" informed.
I don't know my ctrl from my alt!
Many thanks to all
sorted in it!
Cheers

 

[Sandy Mann]

Speaking personally, no offence taken, I just thought that you stood a
better chance of getting an answer from pet because he had actually done
what you were trying to do.

--
Regards,

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Pete_UK]

Have been off-line for a while - looks like Ian has sorted it out.
Basically, the data is text (in an Excel file), so it has to be
converted into date format for his SUM to work.

Pete