How to Convert 5 Digit Number to Time in Excel (4 Quick Tricks)

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.


Download Workbook

Get the practice workbook here and try it yourself.


4 Quick Tricks to Convert 5 Digit Numbers to Time in Excel

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.

How to Convert 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.
=TIMEVALUE(TEXT(C5/100,"00\:00\:00.00"))

1. Use Excel TIMEVALUE Function to Convert 5 Digit Numbers to Time

  • 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.

1. Use Excel TIMEVALUE Function to Convert 5 Digit Numbers to Time

  • After this, select Custom from the Numbers section.
  • Therefore, insert hh:mm:ss.00 as the custom time format and press OK.

1. Use Excel TIMEVALUE Function to Convert 5 Digit Numbers to Time

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.

Read More: How to Convert 4 Digit Number to Time in Excel (3 Methods)


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.
=TIME(INT(C5/1000000),MOD(INT(C5/10000),100),MOD(C5/100,100))

2. Conversion of 5 Digit Number to Time with TIME Function

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.

2. Conversion of 5 Digit Number to Time with TIME Function

  • Now, use the AutoFill tool to use the TIME formula in cell range D6:D9.

Note: If your number string has 3 or 4 digits, then you can use this formula to convert it to time.
=TIME(LEFT(C5,LEN(C5)-2),RIGHT(C5,2),0)

Read More: Convert Number to Hours and Minutes in Excel (2 Easy Methods)


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.
=(INT(C5/10000)&":"&INT(MOD(C5,10000)/100)&":"&MOD(C5,100))+0

How to Convert 5 Digit Number to Time in Excel

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.

3. Apply Excel INT Function to Convert Numbers to Time

  • Then, convert it into hh:mm:ss.00 as we described in the first trick.

3. Apply Excel INT Function to Convert Numbers to Time

  • 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 (2 Easy Methods)


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.

4. Make 5 Digit Numbers to Time with Excel VBA Code

  • 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

4. Make 5 Digit Numbers to Time with Excel VBA Code

  • After that, click on the Run Sub button.

  • Following, insert the Macro Name and press Create.

4. Make 5 Digit Numbers to Time with Excel VBA Code

  • Then, a new window will appear. Delete it and click on the Run Sub again.
  • Now, click on Run.

4. Make 5 Digit Numbers to Time with Excel VBA Code

  • 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:

How to Convert Decimal Numbers to Time in Excel

Now we will convert it to time through this process:

  • At first, divide the cell D5 by 24 like this:

How to Convert Decimal Numbers to Time in Excel

  • It will result in a decimal number.

How to Convert Decimal Numbers to Time in Excel

  • Finally, convert it to hh:mm:ss.00 format as described in the first trick.

How to Convert Decimal Numbers to Time in Excel

  • Use the Autofill tool to convert numbers to time in cell range D6:D9.

Read More: How to Convert Fraction into Hours and Minutes in Excel (3 Ways)


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.

Conclusion

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. Don’t forget to follow ExcelDemy for more excel blogs.


Related Articles

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo