In addition to its basic spreadsheet functions, Microsoft Excel also allows writing formulas to perform functions on existing data. Using a quick equation, you can convert an existing set of time data written in "hh:mm" format to hours in decimal format. After writing the initial equation, copying and pasting it will quickly create a new table with the data in decimal format. Finally, you can copy and paste the new time data over the original.
1. Open an Excel document containing a section with time data written in "hh:mm" format, such as "12:30."
2. Click on an open cell off to the side of the first entry in the existing data. You can use a cell anywhere on the worksheet, but you need an empty area, starting at this cell, equal in size to the existing data table.
3. Type the formula "=(##-INT(##))*24" into the cell. Replace both "##" fields with the cell location of the first cell of time data.
4. Press "Enter" to finish the formula and display the converted decimal form of the first time cell.
5. Click back on the finished cell and press "Ctrl + C" to copy it. This copies the formula, not the decimal output.
6. Move the selector down to the next empty cell, equivalent to the location of the next time data on the original table, and press "Ctrl + V" to paste the formula. The formula will automatically adjust the cell number by the distance it moved from the copied cell, so you won't need to edit it at all.
7. Repeat the pasting process for every empty cell until each cell of time data has a matching decimal cell. For large tables, you can highlight an entire section of blank cells before pressing "Ctrl + V" to fill them all in instead of pasting in each one separately.
8. Highlight the entire completed table of decimal values and copy it with "Ctrl + C." Right-click on the first cell in this copied data and click the "123" icon for "Values" under "Paste Options." This will replace all the formulas with the final decimal data.
9. Re-select and re-copy the entire decimal table, and then highlight the entire original "hh:mm" time table and press "Ctrl + V."
- To reverse the process and convert decimal data into "hh:mm" times, replace the formula with "=TEXT(##/24, "h:mm")" or "=TEXT(##/24, "hh:mm")" if you want single-digit hours to display a preceding zero.
- Comstock/Comstock/Getty Images