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

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.


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.

How to Convert 4 Digit Number to Time in Excel


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 dropdown arrow. And select the Time format.

How to Convert 4 Digit Number to Time in Excel

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

How to Convert 4 Digit Number to Time in Excel

How to Convert 4 Digit Number to Time in Excel

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

How to Convert 4 Digit Number to Time in Excel

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

How to Convert 4 Digit Number to Time in Excel

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

How to Convert 4 Digit Number to Time in Excel


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

Use Excel Functions to Convert 4 Digit Number to Time

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

Use Excel Functions to Convert 4 Digit Number to Time


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.

Using SUBSTITUTE & RIGHT Functions

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

Using SUBSTITUTE & RIGHT Functions


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.

Using INT & MOD Functions

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

Using INT & MOD Functions

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.

Use Custom Formatting

  • Then, select the cells of the time
  • Then, go to the Format options and select the More Number Formats

Use Custom Formatting

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

Use Custom Formatting

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

Use Custom Formatting

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

Use an Excel VBA Macro

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

Use an Excel VBA Macro

  • 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

Use an Excel VBA Macro

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

Use an Excel VBA Macro

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

Use an Excel VBA Macro

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.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo