converting time to seconds

[momo0007]

Hello

I have a column that accepts user input of duration. This should accept values in this format (min:sec), and I need to calculate to convert this to the number of seconds, eg input of 03:00 will return 180 seconds.

However, sometimes when user copies the data from another spreadsheet, the duration is converted to the time format (eg 3:00:00 AM). How then can I convert this to seconds?

Many thanks for your ideas.

 

[shippey121]

to get a formula to do this work for you you will need to get the data input to be the same, in my opinion both the times stated are the same, 3 hours, no minutes, they just look different,

anyway the formula(s) you need to look at are

=hour(a1)
=minute(a1)
=second(a1)

where A1 is the cell with your time,

to convert hours into seconds multiply result by 3600
to convert minutes into second multiply result by 60
then just add other seconds on

examples
=hour(A1)*3600 this will result in seconds
=minute(A1)*60
=second(a1)

or all together

=(hour(A1)*3600)+(minute(A1)*60)+second(a1)