Convert "Time Interval" in "hours : minutes : seconds" to seconds

I wish to compare the cost of telephone calls between various telephone
service providers available to me.
I have downloaded a list of itemised call made by me from my present
provider and I wish to compare the cost of each call if I had used
alternative providers.

I'm OK with the basic mathematical formulae - but I need to convert a "Call
Duration" in "hh:mm:ss" format to seconds.

(I could use the "Text to columns" facility and then multiply the " Minutes
Column by 60 and the hours column by 360 - and add the three columns together
but feel this is clumsy when there must be a "nicer - easier way)

Thanks for your help.

Ian M

Try

=(A1*1440)*60

Where A1 is the time interval

HTH

J

"Ianukotnorth" wrote:

I wish to compare the cost of telephone calls between various telephone
service providers available to me.
I have downloaded a list of itemised call made by me from my present
provider and I wish to compare the cost of each call if I had used
alternative providers.

I'm OK with the basic mathematical formulae - but I need to convert a "Call
Duration" in "hh:mm:ss" format to seconds.

(I could use the "Text to columns" facility and then multiply the " Minutes
Column by 60 and the hours column by 360 - and add the three columns together
but feel this is clumsy when there must be a "nicer - easier way)

Thanks for your help.

Ian M

If A1 contains the duration in hh:mm:ss, say: 10:10:10

Put in B1: =A1*24*60*60
and format B1 as general (or number)

B1 will return the time in seconds: 36610
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik atyahoodotcom
----
"Ianukotnorth" wrote in message
...
I wish to compare the cost of telephone calls between various telephone
service providers available to me.
I have downloaded a list of itemised call made by me from my present
provider and I wish to compare the cost of each call if I had used
alternative providers.

I'm OK with the basic mathematical formulae - but I need to convert a
"Call
Duration" in "hh:mm:ss" format to seconds.

(I could use the "Text to columns" facility and then multiply the "
Minutes
Column by 60 and the hours column by 360 - and add the three columns
together
but feel this is clumsy when there must be a "nicer - easier way)

Thanks for your help.

Ian M

If the time column is in "true" XL recognizable time format as you stated
(hh:mm:ss), then all you have to do is format your total cell, or even each
individual cell (if you choose) to the custom format:
[ss]
The square brackets prevent the seconds from rolling over into minutes, and
then over into hours.

You could also link your individual time cells to a separate column, and
format that column to [ss], so that you might have a displayed comparison.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ianukotnorth" wrote in message
...
I wish to compare the cost of telephone calls between various telephone
service providers available to me.
I have downloaded a list of itemised call made by me from my present
provider and I wish to compare the cost of each call if I had used
alternative providers.

I'm OK with the basic mathematical formulae - but I need to convert a
"Call
Duration" in "hh:mm:ss" format to seconds.

(I could use the "Text to columns" facility and then multiply the "
Minutes
Column by 60 and the hours column by 360 - and add the three columns
together
but feel this is clumsy when there must be a "nicer - easier way)

Thanks for your help.

Ian M

Ragdyer, The problem I find with this method is that you can't then do
calculation on that cell. For example a time interval of 01:00:00 formatted
as [ss] give you 3600 but then in another cell if you try adding 1 to it you
get 1.04, instead of 3601.


J

"Ragdyer" wrote:

If the time column is in "true" XL recognizable time format as you stated
(hh:mm:ss), then all you have to do is format your total cell, or even each
individual cell (if you choose) to the custom format:
[ss]
The square brackets prevent the seconds from rolling over into minutes, and
then over into hours.

You could also link your individual time cells to a separate column, and
format that column to [ss], so that you might have a displayed comparison.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ianukotnorth" wrote in message
...
I wish to compare the cost of telephone calls between various telephone
service providers available to me.
I have downloaded a list of itemised call made by me from my present
provider and I wish to compare the cost of each call if I had used
alternative providers.

I'm OK with the basic mathematical formulae - but I need to convert a
"Call
Duration" in "hh:mm:ss" format to seconds.

(I could use the "Text to columns" facility and then multiply the "
Minutes
Column by 60 and the hours column by 360 - and add the three columns
together
but feel this is clumsy when there must be a "nicer - easier way)

Thanks for your help.

Ian M

You can, you just have to understand it and how it is stored.

You can either use =D14+1/(24*60*60) and also format as [ss] or use
=(D14*86400)+1 and format as General.

--
HTH

Bob Phillips

"Jimbola" wrote in message
...
Ragdyer, The problem I find with this method is that you can't then do
calculation on that cell. For example a time interval of 01:00:00
formatted
as [ss] give you 3600 but then in another cell if you try adding 1 to it
you
get 1.04, instead of 3601.


J

"Ragdyer" wrote:

If the time column is in "true" XL recognizable time format as you
stated
(hh:mm:ss), then all you have to do is format your total cell, or even
each
individual cell (if you choose) to the custom format:
[ss]
The square brackets prevent the seconds from rolling over into minutes,
and
then over into hours.

You could also link your individual time cells to a separate column, and
format that column to [ss], so that you might have a displayed
comparison.
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Ianukotnorth" wrote in message
...
I wish to compare the cost of telephone calls between various
telephone
service providers available to me.
I have downloaded a list of itemised call made by me from my present
provider and I wish to compare the cost of each call if I had used
alternative providers.

I'm OK with the basic mathematical formulae - but I need to convert a
"Call
Duration" in "hh:mm:ss" format to seconds.

(I could use the "Text to columns" facility and then multiply the "
Minutes
Column by 60 and the hours column by 360 - and add the three columns
together
but feel this is clumsy when there must be a "nicer - easier way)

Thanks for your help.

Ian M

Formatting *only* changes the *display* of what the cell contains.
Your 01:00:00 cell contains *one hour*.

When you add 1 to it, what do you *think* that you are adding?
1 day- 1 hour - 1 minute - 1 second ? ? ?

On a new sheet, with 01:00:00 entered in A1, and formatted [ss].
In B1 enter:
=A1+1
You'll get 90000
Which means the 1 (to XL), means one day (8640 seconds), added to one hour
(3600 seconds).

My suggestion was aimed at strictly providing a *display* mode of seconds.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Jimbola" wrote in message
...
Ragdyer, The problem I find with this method is that you can't then do
calculation on that cell. For example a time interval of 01:00:00
formatted
as [ss] give you 3600 but then in another cell if you try adding 1 to it
you
get 1.04, instead of 3601.


J

"Ragdyer" wrote:

If the time column is in "true" XL recognizable time format as you
stated
(hh:mm:ss), then all you have to do is format your total cell, or even
each
individual cell (if you choose) to the custom format:
[ss]
The square brackets prevent the seconds from rolling over into minutes,
and
then over into hours.

You could also link your individual time cells to a separate column, and
format that column to [ss], so that you might have a displayed
comparison.
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Ianukotnorth" wrote in message
...
I wish to compare the cost of telephone calls between various
telephone
service providers available to me.
I have downloaded a list of itemised call made by me from my present
provider and I wish to compare the cost of each call if I had used
alternative providers.

I'm OK with the basic mathematical formulae - but I need to convert a
"Call
Duration" in "hh:mm:ss" format to seconds.

(I could use the "Text to columns" facility and then multiply the "
Minutes
Column by 60 and the hours column by 360 - and add the three columns
together
but feel this is clumsy when there must be a "nicer - easier way)

Thanks for your help.

Ian M

Don't get me wrong, I understand how it works.
I was simply trying to point out that when u use [ss] what you see is not in
a sense what you get.

J

"Ragdyer" wrote:

Formatting *only* changes the *display* of what the cell contains.
Your 01:00:00 cell contains *one hour*.

When you add 1 to it, what do you *think* that you are adding?
1 day- 1 hour - 1 minute - 1 second ? ? ?

On a new sheet, with 01:00:00 entered in A1, and formatted [ss].
In B1 enter:
=A1+1
You'll get 90000
Which means the 1 (to XL), means one day (8640 seconds), added to one hour
(3600 seconds).

My suggestion was aimed at strictly providing a *display* mode of seconds.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Jimbola" wrote in message
...
Ragdyer, The problem I find with this method is that you can't then do
calculation on that cell. For example a time interval of 01:00:00
formatted
as [ss] give you 3600 but then in another cell if you try adding 1 to it
you
get 1.04, instead of 3601.


J

"Ragdyer" wrote:

If the time column is in "true" XL recognizable time format as you
stated
(hh:mm:ss), then all you have to do is format your total cell, or even
each
individual cell (if you choose) to the custom format:
[ss]
The square brackets prevent the seconds from rolling over into minutes,
and
then over into hours.

You could also link your individual time cells to a separate column, and
format that column to [ss], so that you might have a displayed
comparison.
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Ianukotnorth" wrote in message
...
I wish to compare the cost of telephone calls between various
telephone
service providers available to me.
I have downloaded a list of itemised call made by me from my present
provider and I wish to compare the cost of each call if I had used
alternative providers.

I'm OK with the basic mathematical formulae - but I need to convert a
"Call
Duration" in "hh:mm:ss" format to seconds.

(I could use the "Text to columns" facility and then multiply the "
Minutes
Column by 60 and the hours column by 360 - and add the three columns
together
but feel this is clumsy when there must be a "nicer - easier way)

Thanks for your help.

Ian M