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.

**Table of Contents**hide

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

### 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"))`

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

**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))`

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

**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`

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 (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**.

- 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 it to time 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**.

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