converting time to seconds

[Peter]

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.

 

[Bob Phillips]

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)

 

[Bernard Liengme]

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

 

[Niek Otten]

=A1*24*60*60

Time is stored as a fraction of a day in Excel

 

[Peter]

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?

 

[Guest]

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.

 

[Bob Phillips]

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)

 

[Bob Phillips]

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]

OMG! that simple. Thanks for your time.

Peter

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)

 

[Bob Phillips]

It's only simple after you know <vbg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)