How to Convert Time to Number in Excel (5 Easy Methods)

This article demonstrates how to convert time to number in Excel. Excel records time as numbers by default. However, due to the formatting of the cells, you frequently see them in multiple formats. Here, we will take you through 5 easy and convenient methods on how to convert time to number in Excel.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


5 Ways to Convert Time to Number in Excel

Suppose you have a list of time ranges. It contains the Times to Convert into Numbers.

Convert Time to Number in Excel

Now, we’ll convert those times to numbers using various methods in Excel. So, without further delay, let’s jump into the approaches one by one.


1. Engaging Format Cells Option to Convert Time to Number in Excel

Convert Time to Number in Excel Using Format Cells Option

We can apply the Format Cells option to convert our times into numbers. Follow the steps below to understand this method comprehensively.

📌 Steps

  • At first, create a new column in the C4:C12 range. Give it a suitable header. In this case, we named it the Number column.
  • Secondly, copy cells in the C5:C12 range by pressing CTRL+C.

Convert Time to Number in Excel Using Format Cells Option

  • Now, paste these into cell C5.

Convert Time to Number in Excel Using Format Cells Option

  • Then, go to the Home tab.
  • After that, select the Number group.
  • Next, click on the down arrow in the Number Format box.
  • Finally, choose the General format from the list.

Convert Time to Number in Excel Using Format Cells Option

  • At this point, the times are showing as number values.

Convert Time to Number in Excel Using Format Cells Option

We can reduce the digits after the decimal to make it look nicer.

  • Initially, select cells in the C5:C12 range.
  • Again, go to the Home tab.
  • Then, select the Number group.
  • After that, click several times on the Decrease Decimal icon until you get your desired digits after the decimal.

Convert Time to Number in Excel Using Format Cells Option

  • Finally, we converted the times into numbers. And they look like the one below.

Convert Time to Number in Excel Using Format Cells Option


2. Using TIMEVALUE Function to Convert Time to Number in Excel

Convert Time to Number in Excel Using TIMEVALUE Function

An alternative way to do the same job is to use the TIMEVALUE function. Follow the steps below.

📌 Steps

  • At first, select cell C5.
  • Then, write down the formula below and press ENTER.
=TEXT(B5,"H:MM:ss")

Here, B5 represents the time 8:45:12 AM.
In this formula, we used the TEXT function to convert the time in cell B5 into a text string.

Convert Time to Number in Excel Using TIMEVALUE Function

  • Next, use the Fill Handle tool to get the formula in the remaining lower cells.

Convert Time to Number in Excel Using TIMEVALUE Function

  • After that, select cell D5 and type in the formula below.
  • Then, press the ENTER key.
=TIMEVALUE(C5)

Here, C5 represents the text string 8:45:12 in cell C5.
Hence, we used the TIMEVALUE function here. This function takes a text string as input. Then, it returns the text string as a representation of time in decimal number format. The decimal value ranges from 0 to 0.99988426, which corresponds to the hours between 0:00:00 (12:00:00 AM) and 23:59:59 (11:59:59 PM).

Convert Time to Number in Excel Using TIMEVALUE Function

  • Finally, the numbers look like the one below.

Convert Time to Number in Excel Using TIMEVALUE Function


3. Applying Simple Arithmetic Formula

Convert Time to Number in Excel Applying Simple Arithmetic Formula

Also, we can assign simple arithmetic calculations. To solve this way, follow the steps carefully.

📌 Steps

  • At the very beginning, select cell C6.
  • Then, write down the formula below and press ENTER.
=B6*24

Here, B6 serves as the cell reference for the first Time in the list.
In the formula above, we multiplied B6 by 24 as this is the number of hours in a day.

Convert Time to Number in Excel Applying Simple Arithmetic Formula

  • At this point, go to the Home tab.
  • After that, select the Number group.
  • Next, click on the down arrow in the Number Format box.
  • Finally, choose the Number format from the list.

Convert Time to Number in Excel Applying Simple Arithmetic Formula

  • Instantly, it looks like the one below.
  • Finally, the time is converted into the number of hours.

Convert Time to Number in Excel Applying Simple Arithmetic Formula

  • After that, use the Fill Handle tool to get the full results.

Convert Time to Number in Excel Applying Simple Arithmetic Formula

  • Then, select cell D6 and paste down the formula below.
=B6*1440

Here, we multiplied the cell reference of B6 with 1440 as it’s the number of minutes in a whole day.

  • After that, press ENTER.

Convert Time to Number in Excel Applying Simple Arithmetic Formula

  • Similarly, we can get the number in seconds.
  • At first, select cell F6.
  • Then, write down the formula below and press ENTER.
=B6*86400

In such a situation, we multiplied the value of cell B6 by 86400. Because it’s the number of seconds in a whole day.

Convert Time to Number in Excel Applying Simple Arithmetic Formula

  • At last, we converted the Time into the Number of Hours, Minutes, and Seconds.

Convert Time to Number in Excel Applying Simple Arithmetic Formula


4. Implementing HOUR, MINUTE, and SECOND Functions

Convert Time to Number in Excel Using Multiple Functions

We can use multiple Excel functions at a time to solve this problem. Follow our steps below.

📌 Steps

  • Primarily, select cell C6 and put down the formula below.
=HOUR(B6)+MINUTE(B6)/60+SECOND(B6)/3600

Here, B6 serves as the cell reference for the first Time in the list.

⚙️ Formula Breakdown

  • HOUR(B6) The HOUR function returns the hour of a time value. Here, B6 is the serial_number argument. It is the time containing the hour we want to find.
    • Output 8
  • MINUTE(B6)/60 The MINUTE function returns the minutes of a time value. Then, we divided the output by 60. Because we’re calculating the number of hours. Thus, we know that one hour equals sixty minutes.
    • Output 0.75
  • SECOND(B6)/3600 The Second function returns the seconds of a time value. The second is given as an integer in the range 0 to 59. Thus we divided the output by 3600 to get it in hours.
    • Output 0
  • After that, press the ENTER key.

Convert Time to Number in Excel Using Multiple Functions

  • Again, select cell D6 and put down the formula below.
=HOUR(B6)*60+MINUTE(B6)+SECOND(B6)/60

Here, we’re calculating the number in minutes. So, we have to multiply the output of the HOUR function by 60. This converts the value from hours to minutes.

Convert Time to Number in Excel Using Multiple Functions

  • Similarly, we can convert the Time into a number of Seconds.
  • At first, select cell E6 and paste the formula below.
=HOUR(B6)*3600+MINUTE(B6)*60+SECOND(B6)
  • Then, press ENTER.

Implementing HOUR, MINUTE, and SECOND Functions

  • Finally, we converted the Time into Numbers of Hours, Minutes, and Seconds.

Implementing HOUR, MINUTE, and SECOND Functions


5. Employing CONVERT Function

Employing an Excel Function

A very well substitute to convert time into numbers in Excel is to use the CONVERT function. Follow the steps below.

📌 Steps

  • Firstly, select cell C6 and write down the formula below.
=CONVERT(B6,"day","hr")

Here, B6 represents the time 8:45:12 AM.

In this function, the last two arguments are from_unit and to_unit. From_unit is the unit of the input. Whereas, to_unit is the unit for the result. In this case, the day is the from_unit and hr is the to_unit. This function converts the time of the day to just hours.

Employing an Excel Function

  • Similarly, convert the Time to Minutes and Seconds also using the same function. Just change the to_unit to mn and sec respectively.

Employing an Excel Function


Conversion of Time Difference to Numbers

Let’s say, we have a dataset containing the Start Time in Column Cand End Time in Column D of a certain work. Also, Column B holds the Names of the individuals who have done the job.

Conversion of Difference in Numbers

  • From the dataset, we calculated the time difference first.
  • At first, select cell E5 and paste down the formula below.
=D5-C5

Here, C5 and D5 serve as cell references for the first Start and End Time. These are 7:40 AM and 12:25 PM.

  • Then, press the ENTER key.

Conversion of Difference in Numbers

  • At this point, copy cells in the E5:E10 range.
  • Then, go to the Home tab.
  • After that, select Paste in the ribbon as per the image below.
  • Later, select cell F5.
  • Finally, paste the copied data as Values (V).

Conversion of Difference in Numbers

  • Then, change the cell format as we have done before in Method 3.

Conversion of Difference in Numbers

  • Finally, the time difference is shown as numbers on the sheet.

Conversion of Difference in Numbers


Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

Practice Section


Conclusion

Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo