converting time to seconds

momo0007

New Member
Joined
Oct 22, 2006
Messages
19
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
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)
 

Forum statistics

Threads
1,136,877
Messages
5,678,290
Members
419,753
Latest member
Sallylwy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top