If you are searching for a solution or some special tricks to convert a *4 digit* number to time in Excel then you have landed in the right place. Sometimes, you may find time in **HHMM **format as a 4 digit number. This is a 24 hr input as a number without any separation. It also can be of 3 digits. There are several ways to convert 4 digit number to time in Excel. This article will show you each and every step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the main part of the article.

## How to Convert 4 Digit Number to Time in Excel: 3 Methods

Suppose you have a dataset having a column with numbers of 4 digits and this column actually represents time value. So, you want to convert them to time format. In this section, I will show you 3 quick and easy methods to convert 4 digit number to time in Excel on Windows operating system. You will find detailed explanations of methods and formulas here. I have used *Microsoft 365* version here. But you can use any other versions as of your availability. If any methods don’t work in your version then leave us a comment.

### 1. Use Excel Functions to Convert 4 Digit Number to Time

You will find several functions in Excel that you can use to convert 4 digit number to time format. Here, I am showing 5 ways to use Excel functions for this.

#### 1.1 Using ROUNDDOWN and MOD Functions

You can use the **ROUNDDOWN **and **MOD **functions to convert 4 digit numbers to time format. Follow the steps below:

**📌 Steps:**

- First, convert the cells to
*Time.*For this, select the cells and go to the**Format**options by clicking on the drop-down arrow. And select the**Time**format.

- Now, paste this formula into cell
**D5.**

`=ROUNDDOWN(C5,-2)/2400 + MOD(C5,100)/1440`

**🔎 Formula Breakdown:**

Here, I am doing a formula breakdown for cell **D5:**

**ROUNDDOWN(C5,-2) = 100:**It rounds the 4 digit number to nearest**100**.**ROUNDDOWN(C5,-2)/2400= 0.041666667**: it divides the rounded number by 2400 to get the**hour value**in number format. Which is**1:00:00 AM**in time format.**MOD(C5,100) = 45**: It gives the minute value.**MOD(C5,100)/1440 = 0.03125**: It**converts**the minute value from number format to time format. Which is**12:45:00 AM**in Time format**ROUNDDOWN(C5,-2)/2400 + MOD(C5,100)/1440**: It Finally**sums**the hour value and minute value in time format.

- Now drag the
**Fill Handle**icon to paste the used formula respectively to the other cells of the column or use Excel keyboard shortcuts**Ctrl+C**and**Ctrl+P**to copy and paste.

- As a result, you have got the 4 digit numbers in the time format.

**💬 Notes: **

- You can use this way for both 3 digits and 4 digits number to convert in time format.
- You have to convert the cells into time format manually as the formula will give time value in number format

**Read More:** Convert Number to Time hhmmss in Excel

#### 1.2 Using CONCATENATE, RIGHT & LEFT Functions

You also can use the **CONCATENATE**, **RIGHT**, **LEFT**, and **TIMEVALUE** functions to convert the 4-digit numbers to time format. Follow the steps below.

**📌 Steps:**

- Paste this formula into cell
**D5.**

`=TIMEVALUE(CONCATENATE(LEFT(C5,2),":",RIGHT(C5,2)))`

**🔎 Formula Breakdown:**

Here, I am doing a formula breakdown for cell **D5:**

**RIGHT(C5,2) = 45**: The**RIGHT**function will extract the rightmost 2 digits from cell**C5.****LEFT(C5,2) = 01**: The**LEFT**function will extract the leftmost 2 digits from cell**C5.****CONCATENATE(LEFT(C5,2),”:”,RIGHT(C5,2)) = 01:45**: The**CONCATENATE**function combines the values by adding a “:” between them.**TIMEVALUE(CONCATENATE(LEFT(C5,2),”:”,RIGHT(C5,2))) = 1:45:00 AM**: Finally the**TIMEVALUE**function converts the cell into time format.

- Now, drag the fill handle icon and you will get the 4 digit numbers in tie format.

**Read More: **How to Convert Decimal to Time in Excel Using Formula

#### 1.3 Using TEXT Function

You can also use **the TEXT function** to convert the 4 digit numbers to time format. Follow the steps below to convert 4 digit number to hours and minutes in Excel.

**📌 Steps:**

- First, convert the cells to
**Time.** - Then, paste this formula into cell
**D5.**

`=--TEXT(C5,"00\:00")`

** **

**🔎 Formula Breakdown:**

Here, I am doing a formula breakdown for cell **D5:**

**TEXT(C5,”00\:00″) = 01:45 :**The**TEXT**function format the number into**HH:MM**format.**–TEXT(C5,”00\:00″) = 0.072916667 :**The*double dash or minus*sign converts the value into numerical number. And in time format it will give the results as**1:45:00 AM**

- Now, drag the fill handle icon to apply the same formula to the other cells.

#### 1.4 Using SUBSTITUTE & RIGHT Functions

You can also use the **SUBSTITUTE** and **RIGHT** functions with **TIMEVALUE** to convert the 4 digit numbers to time format. Follow the steps below.

**📌 Steps:**

- Paste this formula into cell
**D5**

`=TIMEVALUE(SUBSTITUTE(C5,RIGHT(C5,2),":"&RIGHT(C5,2)))`

**🔎 Formula Breakdown:**

Here, I am doing a formula breakdown for cell D5:

**RIGHT(C5,2) = 45 :**The**RIGHT**function will extract the right most 2 digits of cell C5.**(SUBSTITUTE(C5,RIGHT(C5,2),”:”&RIGHT(C5,2)) = 01:45 :**The**SUBSTITUTE**function will substitute the rightmosr two digits with “:” and the rightmost two digits.**=TIMEVALUE(SUBSTITUTE(C5,RIGHT(C5,2),”:”&RIGHT(C5,2))) = 1:45:00 AM :**Finally, the**TIMEVALUE**function converts the value to the time format.

- Now, drag the
**Fill Handle**icon to apply the same formula to the other cells.

**Read More: **How to Convert Decimal to Minutes and Seconds in Excel

#### 1.5 Using INT & MOD Functions

You can also use the **INT** and **MOD** functions with **the TIME function **to convert the 4 digit numbers to time format. Follow the steps below.

**📌 Steps:**

- First, convert the cells to
**Time.** - Then, paste this formula into cell
**D5.**

`=TIME(INT(C5/100),MOD(C5,100),)`

**🔎 Formula Breakdown: **

Here, I am doing a formula breakdown for cell D5:

**INT(C5/100) = 1 :**Here, the**INT**function will give the hour value of the time.**MOD(C5,100) = 45 :**Here, the**MOD**function will give the minute value of the time.**TIME(INT(C5/100),MOD(C5,100),) = 1:45:00 AM :**Finally, the**TIME**function combines the hour and minute value and gives output in time format.

- Now, paste the same formula into the other cells.

**Read More:** How to Convert 5 Digit Number to Time in Excel

### 2. Apply Custom Formatting

You can also use custom formatting to see the 4 digit numbers in time format. But actually, it won’t become a time value. So, you can’t use them for any calculations. For this, follow the steps below:

**📌 Steps:**

- First, copy the number of cells to the time column.

- Then, select the cells of the
**time.** - Then, go to the
**Format**options and select the**More Number Formats.**

- Now, keep
**remain**in the**number.** - Go to the Custom option in the
**Category.** - Then, insert this in the
**Type**box.

`00":"00`

- Finally, press
**OK**.

- As a result, you will see the cells in
*Time*column converted to**HH:MM**format.

**💬 Note:**

Here, the time column cells are just in** HH:MM** format. They are not giving the time value actually. So, you can’t use them for any calculations.

**Read More: **How to Convert Decimal to Time Over 24 Hours in Excel

### 3. Apply an Excel VBA Macro

You can use VBA macro code to convert the 4 digit numbers to time format. Follow the steps below.

**📌 Steps:**

- For this, first, go to the top ribbon and press on the
**Developer**And then press on the**Visual Basic**option from the menu. - You can use
**ALT + F11**to open the**‘Microsoft Visual Basic for Applications’**window if you don’t have the Developer tab added.

- Now, a window named
**“Microsoft Visual Basic for Applications”**will appear. Here from the top menu bar, press on the**“Insert”**And a menu will appear. From them select the**“Module’”**option.

- Now, a new
**“Module”**window will appear. And**Paste**this VBA code into the box.

```
Sub Time()
With Range("C5", Cells(Rows.Count, "C").End(xlUp)).Resize(, 2)
.Cells = Evaluate(Replace("IF(@="""","""",0+TEXT(@,""00\:00""))", "@", .Address(0, 0)))
.Cells.NumberFormat = "hh:mm"
End With
End Sub
```

- To run the code go to the top menu, press on the
**Run**option, and here will open some other options and**select**the**Run Sub/UserForm**also you can simply press**F5**to run the code.

**💬 Note: **

Here, you have inserted **C5 **as the first cell of the column and applied the code for column **C**. You have to **edit **the **2nd line **of the code as per your dataset position.

- As a result, you will see the Values in the time column converted to time format

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

In this article, you have found how to convert 4 digit number to time in Excel. If you have 24hr input as a 4-digit number then you can apply these methods to convert them into time. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

- How to Convert Fraction into Hours and Minutes in Excel
- Convert Number to Military Time in Excel
- How to Convert Number to Minutes in Excel
- How to Convert Decimal to Days Hours and Minutes in Excel

**<< Go Back to Convert Number to Time | Time Conversion | Date-Time in Excel | Learn Excel**