converting time to seconds

I have been sent a spread sheet where, amongst others, there is a duration
column and a cost column. Duration is shown in hh:mm:ss format (it is not
text as the string is justified to the right of the column). I want to get
cost per minute, but cannot convert the duration to seconds to achieve this.

Grateful for any help on this, please.

multiply by 24 for days, by 60 for hours, by 60 for minutes

*24*60*60

or *86400

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter" <[email protected]> wrote in message
news:[email protected]...
> I have been sent a spread sheet where, amongst others, there is a
duration
> column and a cost column. Duration is shown in hh:mm:ss format (it is not
> text as the string is justified to the right of the column). I want to
get
> cost per minute, but cannot convert the duration to seconds to achieve
this.
>
> Grateful for any help on this, please.
>
>

Hi Peter
Excel stores time as fractions of a day
So if you have 2:30:10 in A1 and you format it General you will see
0.104282407407407
Leaving A1 as either Time or General format, in A2 enter =A1*24*60 (and
format to General in needed) and you will see 150.1667 (2 hr = 120 min, plus
30 min, plus 10/60 = 150.1667)
To get cost per min (time in A1, cost in B1) use =B1/(A1*24*60) {the
parentheses are required)
To show result in dollars & cents (pounds and pence) use
=ROUND(=B1/(A1*24*60),2)

best wishes

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Peter" <[email protected]> wrote in message
news:[email protected]...
>I have been sent a spread sheet where, amongst others, there is a duration
>column and a cost column. Duration is shown in hh:mm:ss format (it is not
>text as the string is justified to the right of the column). I want to get
>cost per minute, but cannot convert the duration to seconds to achieve
>this.
>
> Grateful for any help on this, please.
>

=A1*24*60*60

Time is stored as a fraction of a day in Excel

--
Kind regards,

Niek Otten


"Peter" <[email protected]> wrote in message
news:[email protected]...
>I have been sent a spread sheet where, amongst others, there is a duration
>column and a cost column. Duration is shown in hh:mm:ss format (it is not
>text as the string is justified to the right of the column). I want to get
>cost per minute, but cannot convert the duration to seconds to achieve
>this.
>
> Grateful for any help on this, please.
>

Thanks.
To do this I first have to parse the string to get the hours, mins, secs.
If duration is in cell G5, putting right(G5,2), mid(G5,4,2) and left(G5,2
into discrete columns does not work for me and produces spurious answers.
So how do I get the separate figures to apply the multipliers?


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> multiply by 24 for days, by 60 for hours, by 60 for minutes
>
> *24*60*60
>
> or *86400
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Peter" <[email protected]> wrote in message
> news:[email protected]...
>> I have been sent a spread sheet where, amongst others, there is a
> duration
>> column and a cost column. Duration is shown in hh:mm:ss format (it is
>> not
>> text as the string is justified to the right of the column). I want to
> get
>> cost per minute, but cannot convert the duration to seconds to achieve
> this.
>>
>> Grateful for any help on this, please.
>>
>>
>
>

Because time is in fractions of a day, use something like =A1*24*60*60 if A1
has a time in it.


Be sure to format the cell with the formula as general.
--
Gary's Student


"Peter" wrote:

> I have been sent a spread sheet where, amongst others, there is a duration
> column and a cost column. Duration is shown in hh:mm:ss format (it is not
> text as the string is justified to the right of the column). I want to get
> cost per minute, but cannot convert the duration to seconds to achieve this.
>
> Grateful for any help on this, please.
>
>
>

No, if it is time as you originally said, just multiply. What you see is
just a display format, under the covers the value is a decimal value.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter" <[email protected]> wrote in message
news:%[email protected]...
> Thanks.
> To do this I first have to parse the string to get the hours, mins, secs.
> If duration is in cell G5, putting right(G5,2), mid(G5,4,2) and left(G5,2
> into discrete columns does not work for me and produces spurious answers.
> So how do I get the separate figures to apply the multipliers?
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > multiply by 24 for days, by 60 for hours, by 60 for minutes
> >
> > *24*60*60
> >
> > or *86400
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Peter" <[email protected]> wrote in message
> > news:[email protected]...
> >> I have been sent a spread sheet where, amongst others, there is a
> > duration
> >> column and a cost column. Duration is shown in hh:mm:ss format (it is
> >> not
> >> text as the string is justified to the right of the column). I want to
> > get
> >> cost per minute, but cannot convert the duration to seconds to achieve
> > this.
> >>
> >> Grateful for any help on this, please.
> >>
> >>
> >
> >
>
>

Thanks everyone, but it does not work.I attach an extract from the spread
sheet which demonstrates this. Could some one say why?

Peter


"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> Because time is in fractions of a day, use something like =A1*24*60*60 if
> A1
> has a time in it.
>
>
> Be sure to format the cell with the formula as general.
> --
> Gary's Student
>
>
> "Peter" wrote:
>
>> I have been sent a spread sheet where, amongst others, there is a
>> duration
>> column and a cost column. Duration is shown in hh:mm:ss format (it is
>> not
>> text as the string is justified to the right of the column). I want to
>> get
>> cost per minute, but cannot convert the duration to seconds to achieve
>> this.
>>
>> Grateful for any help on this, please.
>>
>>
>>

Don't forget to format the result cell as general, it will default to time.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter" <[email protected]> wrote in message
news:[email protected]...
> Thanks everyone, but it does not work.I attach an extract from the spread
> sheet which demonstrates this. Could some one say why?
>
> Peter
>
>
> "Gary''s Student" <[email protected]> wrote in
message
> news:[email protected]...
> > Because time is in fractions of a day, use something like =A1*24*60*60
if
> > A1
> > has a time in it.
> >
> >
> > Be sure to format the cell with the formula as general.
> > --
> > Gary's Student
> >
> >
> > "Peter" wrote:
> >
> >> I have been sent a spread sheet where, amongst others, there is a
> >> duration
> >> column and a cost column. Duration is shown in hh:mm:ss format (it is
> >> not
> >> text as the string is justified to the right of the column). I want to
> >> get
> >> cost per minute, but cannot convert the duration to seconds to achieve
> >> this.
> >>
> >> Grateful for any help on this, please.
> >>
> >>
> >>
>
>
>

OMG! that simple. Thanks for your time.

Peter

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Don't forget to format the result cell as general, it will default to
> time.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Peter" <[email protected]> wrote in message
> news:[email protected]...
>> Thanks everyone, but it does not work.I attach an extract from the spread
>> sheet which demonstrates this. Could some one say why?
>>
>> Peter
>>
>>
>> "Gary''s Student" <[email protected]> wrote in
> message
>> news:[email protected]...
>> > Because time is in fractions of a day, use something like =A1*24*60*60
> if
>> > A1
>> > has a time in it.
>> >
>> >
>> > Be sure to format the cell with the formula as general.
>> > --
>> > Gary's Student
>> >
>> >
>> > "Peter" wrote:
>> >
>> >> I have been sent a spread sheet where, amongst others, there is a
>> >> duration
>> >> column and a cost column. Duration is shown in hh:mm:ss format (it is
>> >> not
>> >> text as the string is justified to the right of the column). I want
>> >> to
>> >> get
>> >> cost per minute, but cannot convert the duration to seconds to achieve
>> >> this.
>> >>
>> >> Grateful for any help on this, please.
>> >>
>> >>
>> >>
>>
>>
>>
>
>

It's only simple after you know <vbg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter" <[email protected]> wrote in message
news:[email protected]...
> OMG! that simple. Thanks for your time.
>
> Peter
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > Don't forget to format the result cell as general, it will default to
> > time.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Peter" <[email protected]> wrote in message
> > news:[email protected]...
> >> Thanks everyone, but it does not work.I attach an extract from the
spread
> >> sheet which demonstrates this. Could some one say why?
> >>
> >> Peter
> >>
> >>
> >> "Gary''s Student" <[email protected]> wrote in
> > message
> >> news:[email protected]...
> >> > Because time is in fractions of a day, use something like
=A1*24*60*60
> > if
> >> > A1
> >> > has a time in it.
> >> >
> >> >
> >> > Be sure to format the cell with the formula as general.
> >> > --
> >> > Gary's Student
> >> >
> >> >
> >> > "Peter" wrote:
> >> >
> >> >> I have been sent a spread sheet where, amongst others, there is a
> >> >> duration
> >> >> column and a cost column. Duration is shown in hh:mm:ss format (it
is
> >> >> not
> >> >> text as the string is justified to the right of the column). I want
> >> >> to
> >> >> get
> >> >> cost per minute, but cannot convert the duration to seconds to
achieve
> >> >> this.
> >> >>
> >> >> Grateful for any help on this, please.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >
> >
>
>