If you are searching for the 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.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here:

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

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 function** and **MOD function** 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 (2 Suitable Ways)**

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

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

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

#### 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 (4 Quick Tricks)**

### 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 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:** **Format Hours and Minutes Not Time in Excel (with Quick Steps)**

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

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

## 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. You can visit our website** ExcelDemy** to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.