Convert Time to Seconds in Excel
Helps to find the total number of seconds in a given time
how to convert time to seconds in Excel?
Excel provides us with tools and formulas to change or manipulate our data. One such important and frequently used data is Time. Time is very important to keep track of your tasks. It helps you organize and remember things you may have forgotten.
You can do a lot of things with Time in Excel. One such tool is to convert time to seconds.
You may need to find the total number of seconds in a given time. Knowing how to convert Time into seconds will be handy in such situations. Below you will find three different methods to achieve this.
You will also find information on how to convert seconds into Time. Finally, you will also see how to convert Time into hours and minutes using different methods. Other conversions like Text and number are also shown below with examples.
Key Takeaways
- The converted cell should be in general format when using the Formula method.
- To convert decimals into integers, use INT to reduce decimals instead of the decrease decimal button, as this will round off to the nearest integer and may display incorrect hour or minute.
- Always ensure that the cell with time is in the time format, especially when converting the number to time.
- In the formula, the cell reference must be the cell with data. Therefore, ensure you refer to the correct cell, which may not be the same as used in the example.
Arithmetic Method
There are three different methods to extract seconds, minutes, or hours. The first and quickest method is a simple calculation to extract seconds from a given time.
1. Seconds
The number 86400 is arrived at after multiplying the total number of hours in a day with the total minutes in an hour into the total number of seconds in a minute.
24 hours * 60 minutes * 60 seconds = 86400
We can multiply the Time with this number to convert it into seconds. Below you will see an example of this.
2. Minutes
We can even find the total minutes from a given time by multiplying the Time by 1440, which is the total number of minutes in a day.
24 hours * 60 minutes = 1440
We can multiply the Time with this number to convert it into seconds. Below you will see an example of this.
Here you will see that the minutes are in the form of a decimal. To get the minutes in integer form, use this formula.
=INT(D2*1440)
3. Hours
Identifying hours is easy compared to minutes and seconds, but you may get a fraction compared to the total 24 hours if minutes and seconds are also present.
Below you will see an example of a fraction of the total hours.
Here you will see that the hours are in the form of a decimal. To get the hours in integer form, use this formula.
=INT(D2*24)
Note
Remember to change the result format into general or number to see the correct answer.
Formula Method
We can also use a formula for conversion. Excel has a formula that converts one unit into another, known as the Convert function. It can convert Time into hours, minutes, or seconds.
The function requires a cell reference and two arguments: the unit to be converted and the unit to be converted into.
1. Seconds
The 'from' unit selected is the day, and the 'to' unit is sec for seconds. The formula to convert to seconds is as follows.
=CONVERT(D2, "day", "sec")
Here, in the example below, we select D2 as our cell with data and input the 'from' unit and 'to' unit.
2. Minutes
The 'from' unit will be the same, and the 'to' unit is mn for minutes. The formula for minutes is as follows.
=CONVERT(D2, "day", "mn")
Here, in the example below, we select D2 as our cell with data and input the 'from' unit and 'to' unit.
Here, you will see that the minutes are in the form of a decimal. To get the minutes in integer form, use this formula.
=INT(E2)
Note
Do not use the decrease decimal button instead of the INT function, as the decrease decimal rounds off to the nearest integer and may display an incorrect answer.
3. Hours
The 'from' unit will be the same, and the 'to' unit will be hr for hours. The formula for hours is as follows.
=CONVERT(D2, "day", "hr")
Here, in the example below, we select D2 as our cell with data and input the 'from' unit and 'to' unit.
Here you will see that the hours are in the form of a decimal. To get the hours in integer form, use this formula.
=INT(E2)
Note
Do not use the decrease decimal button instead of the INT function. As the decrease, the decimal rounds off to the nearest integer and may display an incorrect answer.
Arithmetic and Formula in Excel
This is another way of calculating the total seconds of a given time. It involves formulas and calculations.
Here we will use three different functions, The Hour function to extract the hours from the cell, the Minute function to extract the minutes from the cell, and the Second function to extract the seconds.
The formula is simple but uses three functions.
1. Hour: This function extracts hours from a time. We will multiply it by the total number of seconds (36000) in an hour to arrive at the number of seconds in the hours mentioned in the Time.
2. Minute: This function extracts minutes from a time. We will multiply it by the total number of seconds (60) in a minute to arrive at the number of seconds in the minutes mentioned in the Time.
3. Second: This function extracts seconds from a time.
We will add these three functions together to arrive at the number of seconds in the given Time.
You can see the formula below for a better understanding.
=HOUR(D2) * 3600 + MINUTE(D2) * 60 + SECOND(D2)
Excel displays the result in Time format due to the same reference cell. Ensure that the format of the cell is General or Number.
Convert Time to Seconds, Minutes and Hours
We have seen different ways of extracting various parts of Time, not the other way around. However, dividing them with the respective calculation can also be achieved easily.
1. Seconds
We saw above that to convert Time; we multiplied it by the total number of seconds in a day. So to convert seconds, you must divide the seconds by the total number of seconds, 86400.
24 hours * 60 minutes * 60 seconds = 86400
We can divide the Time with this number to convert it into seconds; for example,
2. Minutes
Similarly, to convert it to minutes, we will divide it by 1440, the total number of minutes in a day.
24 hours * 60 minutes = 1440
We can divide the Time with this number to convert it into minutes; for example,
3. Hours
For Hours we will divide the hour by 24, the total number of hours in a day, to arrive at the answer.
Remember to change the result format into general or number to see the correct answer.
Other Time Conversions
The above examples show how to convert Time to other time formats. Now you may ask if we can convert Time into a text, or you may want a number to be shown as Time.
We've got you covered; listed below are other conversions you can do in Excel with Time.
1. Convert Time to Text
You will see that it turns into a decimal format whenever you try to format Time as a text. However, there is an alternative to bypass this: the Text function.
The Text function converts numeric data into Text with the desired display format. Here, in the formula, we have typed the time format we wanted to display.
You can choose any time format under the custom category in the format cells box and copy and paste it into the formula.
=TEXT(D2, "h:mm:ss AM/PM")
Below, you will see how we can use this to transform our Time into Text.
2. Convert Text to Time
Similarly, you may encounter a set of data where the Time is in text format, maybe because it was imported into excel or you want to undo your previous step of transforming Time to Text. It is time-consuming and cumbersome to change the format of each cell manually.
The TimeValue function can help you solve this problem. It converts Text into a numerical representation of Time. You can format the cell into Time and get the final result. It is a simple function that only requires the cell containing the Text.
=TIMEVALUE(D2)
Below is an example of this function.
3. Convert the Number to Time format.
If not specified as a time format, Excel will display a numeric representation of it in decimal format. Solving this is simple; you just have to open the format cell box and select the Time format.
You can also convert an integer into Time by dividing it by 24. The number 24 denotes the total hours in a day.
When we divide the number by 24, we get a decimal number in the cell E2. You see that the cell format is set as General by Excel.
Here in cell F2, we see that the same calculation displays a time rather than a number because of the format change.
or Want to Sign up with your social account?