Formula to Convert Minutes:Seconds to just seconds - Plz read Post

[Notts_Lad]

Hi Gang,

I will explain best way i can.

If Cell A1 contains the time 01:30, thats 1 minute & 30 Seconds, Then
need cell B1 to display the value as just seconds so that it looks lik
this: 90.

Again if a user types 01:00 in Cell A1 then Cell B1 should convert an
display this as 120.

I know the basic maths behind this, using the 01:30 as an example
First excel should take what ever is before the : and multiply by 6
(to convert the minutes into seconds, then it should add the result t
the number AFTER the : (which are seconds anyway and the end result i
90, and 90 is what should be displayed in cell B1.

My problem is i do not know how to tell excell to do what I want, coul
any of you kindly offer me some help, you would really be helping me i
you did.

Please accept my thanks in advance

Kenn

 

[Domenic]

Hi Kenny,

=A1

Format > Cells > Number > Custom > Type:

Hope this helps

 

[Don Guillett]

try to multiply by 24 and then by 60 (1440)
=a1*1440

 

[Earl Kiosterud]

Kenny,

You can just make a mirror of the cell, and use formatting to show it in
seconds.

B1: =A1
B1: Format - Cells - Number - Custom: [ss]

Note that if the user types 1:30, it will be interpreted as hh:mm, not
mm:ss, and will show 5400 seconds. It's necessary to type 0:1:30.

 

[CWatters]

If the other suggestions don't do what you want play a round with something
like...

=60*VALUE(LEFT(A1,2))
+
VALUE(RIGHT(A1,2))

Hint...

LEFT(string,n) returns the n chars from the left of the string.

Value converts text to number

 

[SidBord]

This isn't going to solve your problem, but it might give
you a starting point:

=Left(Text(Timevalue("01:30:00 AM"),"hh:mm"),2)*60 + _
Right(Text("01:30"),"hh:mm"),2)
This formula yield 90 on my computer.
When I substitute a cell address for the time string, it
has an error, but I suspect if I kept playing with it I
could find a solution.

 

[Notts_Lad]

Hi Gang,

Thanks again to all of you for your suggestions, I have now managed b
luck and some suggestions from experts like yourselves, managed t
obtain a formula that works for me

If you assume that the time value is entered into Cell A1 (which i
formatted as a text cell) as mm:ss.
So for example: 1:30 = 1 minute & 30 Seconds


The formula, which I entered into Cell B1 is:
=RIGHT(A4,2)+LEFT(A4,LEN(A4)-3)*60

It works fine here for me and I thought I'd share this solution wit
the forum in case anyone was interested.

Thanks again guys, I am sure that I willk have more questions for yo
in the future as Excel is a relitivly new program to me but, with you
help, I am learning.

Regards,

Kenn