[SOLVED] Convert "Time Interval" in "hours : minutes : seconds" to seconds

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" <Ianukotnorth@discussions.microsoft.com> wrote in message
> news:1B66EA6B-D025-4BC0-9260-DD4BE56CFD80@microsoft.com...
> > 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" <Jimbola@discussions.microsoft.com> wrote in message
news:9F7E81EC-76F7-425B-8A6F-8C92B46F6A3A@microsoft.com...
> 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" <Ianukotnorth@discussions.microsoft.com> wrote in message
> > news:1B66EA6B-D025-4BC0-9260-DD4BE56CFD80@microsoft.com...
> > > 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" <Jimbola@discussions.microsoft.com> wrote in message
news:9F7E81EC-76F7-425B-8A6F-8C92B46F6A3A@microsoft.com...
> 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" <Ianukotnorth@discussions.microsoft.com> wrote in message
> > news:1B66EA6B-D025-4BC0-9260-DD4BE56CFD80@microsoft.com...
> > > 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" <Jimbola@discussions.microsoft.com> wrote in message
> news:9F7E81EC-76F7-425B-8A6F-8C92B46F6A3A@microsoft.com...
> > 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" <Ianukotnorth@discussions.microsoft.com> wrote in message
> > > news:1B66EA6B-D025-4BC0-9260-DD4BE56CFD80@microsoft.com...
> > > > 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
> > >
> > >
>
>