How to Change Time Format in Excel using Formulas
How to Change Time Format in Excel using Formulas. Excel stores time as a decimal number, where 0.412 represents one hour (which is 1/24). You can change the time format in Excel using two different ways.
Apply Time Format in Excel on the Same Cell or Create a New Time Value
To apply a time format to the same cell, follow these steps:
- Select the cell with the decimal value.
- Use the keyboard shortcut Ctrl + 1 to open the format options.
- From the list of format options, select the time option.
- Select the desired from the options on the right side of the dialog box.
- Click OK to apply the format to the selected cell.
Alternatively, you can create a new time value in a different cell by using a formula. For example, if the decimal value is in cell A1, you can use the formula =A1*24 to get the time value in hours, and then apply a time format to the cell containing the formula.
Use the TEXT Function to Change the Time Format
To change the time format using the TEXT function, follow these steps:
- In a new cell, enter the formula =TEXT(A1, “hh:mm AM/PM”) where A1 is the cell containing the decimal value you want to format.
- Replace “hh:mm AM/PM” with the desired time format code. For example, “hh:mm:ss” for hours, minutes, and seconds, or “h:mm AM/PM” for hours in 12-hour clock format.
- Press Enter to apply the formula and get the result in the new cell.
Using the TEXT function allows you to change the time format without modifying the original data, which can be useful when working with large datasets.
