How to Convert Time Duration to Seconds in Excel

This guide will discuss how to convert time duration to seconds in Excel using two easy methods

Excel is an excellent tool to use for different situations and purposes. Since it contains several built-in functions and tools, we can easily perform complicated calculations and manipulate data. 

For instance, we can easily convert our data from one measurement system to another measurement system. In this guide, we want to learn how to convert time duration to seconds in Excel.

Since Excel uses a numeric system to input time, we can easily convert hours, minutes, and seconds into numbers or decimals that we can use in other formulas or calculations.

Luckily, there are two simple ways we can convert time duration to seconds in Excel. Firstly, we can simply use the traditional arithmetic way of multiplication. And the other method would utilize the CONVERT function.

Let’s take a sample scenario wherein we need to convert time duration to seconds in Excel.

Suppose you are a PE teacher who is conducting an obstacle course among the students in class. So you are recording the time duration or the amount of time it took for a student to finish the obstacle course. Moreover, you want to rank the students and display their time duration in seconds.

To do this, you simply multiplied the time duration of each student by 86400. Then, you successfully converted the time duration into seconds.

Before we move on to a real example of converting time duration to seconds in Excel, let’s first learn how to use the CONVERT function.

 

The Anatomy of the CONVERT Function

The syntax or the way we write the CONVERT function is as follows:

=CONVERT(number, from_unit, to_unit)

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we activate any function in Excel.
  • CONVERT() is our CONVERT function. And this function is used to return a number from one measurement system to another. 
  • number is a required argument. So this refers to the value we want to convert to another measurement system.
  • from_unit is also a required argument. And this refers to the original unit of the number we want to convert.
  • to_unit is another required argument. So this refers to the unit we want the number to be converted into or the unit for the result. 

Great! Now we can move on and dive into a real example of converting time duration to seconds in Excel.

 

A Real Example of Converting Time Duration to Seconds in Excel

Let’s say we have a data set containing the different time duration it took for employees to finish a certain task. However, we want to turn the time duration into seconds for us to easily use in calculations. So our initial data set would look like this:

Initial dataset

 

Since our goal is to convert the time duration in our data set into seconds, we can utilize two easy and simple methods. Firstly, we can simply do the arithmetic way of multiplication. To do this, we simply multiply the time duration by 86400 to convert it to seconds. 

So Excel uses a 24-hour system meaning each day has 24 hours. And one hour has 60 minutes. Lastly, one minute has 60 seconds. When we want to convert time duration to seconds, we would use the value 24(hours)*60(minutes)*60(seconds) = 86400.

Secondly, we can utilize the CONVERT function. So the CONVERT function is used to change the measurement system of a number to another measurement system. In this case, we want to change the time duration, which can be day to another unit which is seconds. 

Furthermore, the CONVERT function is case-sensitive. If a unit string we input is not recognized, the function will return a #N/A error. If the units we want to convert are not compatible, the function will return a #N/A error. When a number is not valid, the function will return a #VALUE! error. 

Before converting our time duration to seconds, we first need to ensure the cells containing the time duration are properly formatted. To do this, we simply go to the number format dialogue box. Then, we make sure that Excel recognizes the time duration as time and follows the format hh:mm:ss. 

Afterward, we can choose any of the methods to convert the time duration to seconds. For instance, we can simply multiply the time duration values by 86400. 

Otherwise, we can also use the CONVERT function to change the time duration to seconds using the argument =CONVERT(number, ”day”, ”sec”). So our final data set would look like this:

Final dataset

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can proceed to discuss the steps of how to convert time duration to seconds in Excel using two simple and easy methods.

 

How to Convert Time Duration to Seconds in Excel

In this section, we will discuss the step-by-step process of how to convert time duration to seconds in Excel using two easy and simple methods. Furthermore, each step has detailed instructions and pictures to guide you along the way.

To apply this method to your work, simply follow the steps below.

1. Firstly, we need to make sure the cells containing the time duration are in the proper format. To do this, we can simply select the entire column containing the time duration and right-click. Then, we will click Number Format.

Change format

 

2. In the Number Format window, we will select Time under the Category. Next, we will choose the time format that follows hh:mm:ss without the AM or PM label. Lastly, we will click OK to apply the changes.

Proper time format

 

3. Thirdly, we can now convert the time duration to seconds using multiplication. To do this, we can simply type in the formula “=C2*86400”. Then, we will press the Enter key to return the result.

Time Duration to Seconds in Excel

 

4. Next, we can drag down the Fill Handle tool to copy and apply the formula to the rest of the cells.

Time Duration to Seconds in Excel

 

5. Afterward, let’s also try using the CONVERT function. To do this, we can simply input the formula “=CONVERT(C2, “day”, “sec”)”. Lastly, we will press the Enter key to return the result.

Time Duration to Seconds in Excel

 

6. Then, we will drag the Fill Handle tool down to copy the formula to the other cells.

Time Duration to Seconds in Excel

 

7. And tada! We have successfully converted time duration to seconds in Excel.

Time Duration to Seconds in Excel

 

And that’s pretty much it! We have successfully discussed how to convert time duration to seconds in Excel using two simple and easy methods. Now you can choose any of the two methods and apply it to your work whenever you need.

Are you interested in learning more about what Excel can do? You can now use the CONVERT function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like