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.
