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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

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

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


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

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

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

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.

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


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.

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

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


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo