When you work in an Excel sheet, especially with long data series, it often happens that you may forget the format in which you want to insert your data. It mostly occurs in the case of time. At that point, you need to convert your regular number to time. But it is very difficult to change them individually when the data series is very long. This is why we will learn in this article how to convert a 5 digit number to time in Excel with 4 quick tricks.
How to Convert 5 Digit Number to Time in Excel: 4 Quick Tricks
For example, here is a dataset that shows the time in a string of 5 digit numbers defining the duration of use of a gadget in a week. But we need to convert these numbers to time to get the actual data in three parameters- hours, minutes and seconds. Let’s see 4 quick tricks to convert a 5 digit number to time in Excel.
1. Use Excel TIMEVALUE Function to Convert 5 Digit Numbers to Time
The first trick to convert 5 digit numbers to time is using the TIMEVALUE function in Excel. Follow to steps below:
- First, insert this TIMEVALUE function in cell D5.
- Then, press Enter. It will show the result in a decimal number like this:
- Now, select cell D5 and right-click.
- Then, choose Format Cells from the Context Menu.
- After this, select Custom from the Numbers section.
- Therefore, insert hh:mm:ss.00 as the custom time format and press OK.
Here, we added 00 in the time parameters to add milliseconds in the result.
- Finally, you can see the numbers are converted to time as we wanted the duration of use of the gadget.
- Now, use the AutoFill tool to get the times in cell range D6:D9.
- Explore the Format Cells dialogue box to illustrate the format of time according to the dataset.
2. Conversion of 5 Digit Number to Time with TIME Function
In this section, we will use the TIME function in Excel to convert the 5 digit numbers to time. Let’s see how to do this.
- In the beginning, input the TIME function in cell D5.
Here, the INT function is added to round down the closest integer when the hours, minutes and seconds are divided by 1000000, 10000, and 100 respectively. Also, the MOD function is applied here to get the remainder when a number is divided by another number.
- After that, press Enter to see the result.
- Here, the numbers have automatically converted to hh:mm:ss.00 format.
- Now, use the AutoFill tool to use the TIME formula in cell range D6:D9.
Read More: Convert Number to Time hhmmss in Excel
3. Apply Excel INT Function to Convert Numbers to Time
The third trick is to apply the INT function in Excel to convert numbers to time. Here is the step-by-step process:
- First, insert the INT function in cell D5.
Here, the INT function is used to round down the closest integer when the hours, minutes and seconds are calculated with 10000 and 100. Also, the MOD function is applied here to get the remainder when a number is divided by another number.
- Secondly, press Enter. It shows the result in a decimal number.
- Then, convert it into hh:mm:ss.00 as we described in the first trick.
- Now, use the AutoFill tool to convert the numbers to time in the range of cells D6:D9.
Read More: How to Convert Number to Minutes in Excel
4. Make 5 Digit Numbers to Time with Excel VBA Code
In this last one, we will use Excel VBA code to convert 5 digit numbers to time. Let’s see how to do that:
- To begin the process, select the worksheet from the Sheet bar and right-click on it.
- Then, choose View Code from its Context Menu.
- Now, insert this VBA code in the blank worksheet that appeared.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim iSt As String Dim gVl As String On Error GoTo EndMacro If Application.Intersect(Target, Range("C5:C9")) Is Nothing Then Exit Sub If Target.Count > 1 Then Exit Sub If Target.Value = "" Then Exit Sub Application.EnableEvents = False With Target If Not .HasFormula Then gVl = .Value Select Case Len(gVl) Case 1 ' e.g., 1 = 00:01 AM iSt = "00:0" & gVl Case 2 ' e.g., 12 = 00:12 AM iSt = "00:" & gVl Case 3 ' e.g., 735 = 7:35 AM iSt = Left(gVl, 1) & ":" & Right(gVl, 2) Case 4 ' e.g., 1234 = 12:34 iSt = Left(gVl, 2) & ":" & Right(gVl, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 iSt = Left(gVl, 1) & ":" & Mid(gVl, 2, 2) & ":" & Right(gVl, 2) Case 6 ' e.g., 123456 = 12:34:56 iSt = Left(gVl, 2) & ":" & Mid(gVl, 3, 2) & ":" & Right(gVl, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(iSt) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "Invalid Time" Application.EnableEvents = True End Sub
- After that, click on the Run Sub button.
- Following, insert the Macro Name and press Create.
- Then, a new window will appear. Delete it and click on the Run Sub again.
- Now, click on Run.
- At this stage, insert the 5 digit number in cell C5 manually and press Enter.
- Finally, you can see it automatically converts to time.
- As this worksheet is incorporated into the VBA code, you can just insert each number in cells C6:C9 manually and it will automatically convert it into time.
How to Convert Decimal Numbers to Time in Excel
In this case, let’s assume we have the numbers like this:
Now we will convert decimal to time in Excel using the formula through this process:
- At first, divide the cell D5 by 24 like this:
- It will result in a decimal number.
- Finally, convert it to hh:mm:ss.00 format as described in the first trick.
- Use the AutoFill tool to convert numbers to time in cell range D6:D9.
Things to Remember
- The pattern of illustration will depend on the functions and formats used. So be conscious about your objective.
- Keep in mind that the INT function targets to round down the result. Therefore, it does not show any fraction numbers on the right side.
Get the practice workbook here and try it yourself.
Here, we learned 4 quick tricks to convert a 5 digit number to time in Excel. Download the practice workbook and go through each process to get your required result. If you have any further queries or recommendations, please feel free to comment here.