How to Convert a 4 Digit Number to Time in Excel

The sample dataset contains 4 digits that represents time value in column C.

Method 1 – Use Excel Functions to Convert the 4 Digit Number to Time

1.1 Using ROUNDDOWN and MOD Functions

Steps:

Select the cells and select Format.
Choose Time.

Enter the formula in D5.

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

Formula Breakdown:

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

Drag down the Fill Handle to see the result in the rest of the cells or press Ctrl+C and Ctrl+P to copy and paste.

This is the output.

Notes:

You can also use this method for 3 digit numbers.

1.2 Using the CONCATENATE, RIGHT and LEFT Functions

Steps:

Enter the formula in D5.

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

Formula Breakdown:

RIGHT(C5,2) = 45 :extracts the rightmost 2 digits in C5.
LEFT(C5,2) = 01 : extracts the leftmost 2 digits in C5.
CONCATENATE(LEFT(C5,2),”:”,RIGHT(C5,2)) = 01:45 : combines the values by adding a “:” between them.
TIMEVALUE(CONCATENATE(LEFT(C5,2),”:”,RIGHT(C5,2))) = 1:45:00 AM : converts the cell into time format.

Drag down the Fill Handle to see the result in the rest of the cells.

1.3 Using TEXT Function

Steps:

Convert the cells to Time format.
Enter the formula in D5.

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

Formula Breakdown:

TEXT(C5,”00\:00″) = 01:45 : formats 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 returns the time format: 1:45:00 AM

Drag down the Fill Handle to see the result in the rest of the cells.

1.4 Using SUBSTITUTE & RIGHT Functions

Steps:

Enter the formula in D5.

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

Formula Breakdown:

RIGHT(C5,2) = 45 : extracts the rightmost 2 digits in C5.
(SUBSTITUTE(C5,RIGHT(C5,2),”:”&RIGHT(C5,2)) = 01:45 : replaces the rightmost two digits with “:” and the rightmost two digits.
=TIMEVALUE(SUBSTITUTE(C5,RIGHT(C5,2),”:”&RIGHT(C5,2))) = 1:45:00 AM : converts the value to time format.

Drag down the Fill Handle to see the result in the rest of the cells.

1.5 Using INT & MOD Functions

Steps:

Convert the cells to Time format.
Enter the formula in D5.

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

Formula Breakdown:

INT(C5/100) = 1 : returns the hour value.
MOD(C5,100) = 45 : returns the minute value.
TIME(INT(C5/100),MOD(C5,100),) = 1:45:00 AM : combines the hour and minute values.

Paste the formula into the other cells.

Method 2 – Applying a Custom Formatting

Steps:

Copy column C to the Time column.

Select the cells in the Time column.
Go to Format and select More Number Formats.

Keep remain in number.
Select Custom in Category.
Enter the formula in Type.

00":"00

Click OK.

You will see the cells in the Time column converted to HH:MM format.

Method 3 – Apply an Excel VBA Macro

Steps:

Go to Developer and select Visual Basic. You can also press ALT + F11 to open the ‘Microsoft Visual Basic for Applications’ window.

Select “Insert” and choose “Module’”.

Enter this VBA code:

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