How to Convert a Number to Time in Excel

Save

Microsoft Excel stores all dates and times as numbers, so that the number "1" is January 1, 1900, and each day after that is one number higher. Time is recorded as a fraction of the date, so "0.5" would be 12 noon, and "0.25" would be 6 a.m. Once you understand this concept, you can create a simple formula that will convert a number like "10.5" into "10:30."

  • Open the Excel file that contains the numbers you want to convert into times.

  • Select the empty cell to the right of the cell where the number is located. If that cell isn't empty, right-click the letter at the top of that column. Choose "Insert" from the pop-up menu and a blank column will appear. Then, you can select your empty cell.

  • Enter the following formula into the cell, leaving out the quotes: "=TEXT(XX/24, "h:mm"). "XX" is the column letter and row number of the cell where your number is located.

  • Press "Enter" to complete the formula. The cell will display the number that resides in your target cell, but it will display it as time.

Tips & Warnings

  • You can change "h:mm" in the formula to "h:mm:ss" if you need the seconds to display as well.

References

Promoted By Zergnet

Comments

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!