Loading

Got a version of Excel that uses the
menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)?
**This site is for you!** If you
use a later version of Excel, visit
our *ExcelTips* site focusing on the ribbon interface.

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.

Learn more about Allen...

ExcelTips FAQ

ExcelTips Resources

Ask an Excel Question

Make a Comment

Free Business Forms

Free Calendars

** Please Note:** This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later),

Sam has a lot of worksheets that contain times. The problem is that the times are in the format "1300" instead of the format "13:00." Thus, Excel sees them as regular numeric values instead of recognizing them as times. Sam wants them to be converted to actual time values.

There are several ways you can approach this task. One way is to use the TIME function to convert the value to a time, as shown here:

=TIME(LEFT(A1,2),RIGHT(A1,2),)

This formula assumes that the time in cell A1 will always contain four digits. If it does not (for instance, it might be 427 instead of 0427), then the formula needs to be modified slightly:

=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),)

The formula basically pulls the leftmost digit (or digits) and uses them for the hours argument of the TIME function, and then uses the two rightmost digits for the minutes argument. TIME returns an actual time value, formatted as such in the cell.

A similar formulaic approach can be taken using the TIMEVALUE function:

=TIMEVALUE(REPLACE(A1,LEN(A1)-1,0,":"))

This formula uses REPLACE to insert a colon in the proper place, and then TIMEVALUE converts the result into a time value. You will need to format the resulting cell so that it displays the time as you want.

Another variation on the formulaic approach is to use the TEXT function, in this manner:

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

This returns an actual time value, which you will then need to format properly to be displayed as a time.

Another approach is to simply do the math on the original time to convert it to a time value used by Excel. This is easy once you realize that time values are nothing more than a factional part of a day. Thus, a time value is a number between 0 and 1, derived by dividing the hours by 24 (the hours in a day) and the minutes by 1440 (the minutes in a day). Here is a formula that does that:

=INT(A1/100)/24+MOD(A1,100)/1440

This determines the hour portion of the original value, which is then divided by 24. The minute portion (the part left over from the original value) is then divided by 1440 and added to the first part. You can then format the result as a time, and it works perfectly.

All of the formulas described so far utilize a new column in order to do the conversions. This is handy, but you may want to actually convert the value in-place, without the need for a formula. This is where a macro can come in handy. The follow macro will convert whatever cells you have selected into time values and format the cells appropriately:

Sub NumberToTime() Dim rCell As Range Dim iHours As Integer Dim iMins As Integer For Each rCell In Selection If IsNumeric(rCell.Value) And Len(rCell.Value) > 0 Then iHours = rCell.Value \ 100 iMins = rCell.Value Mod 100 rCell.Value = (iHours + iMins / 60) / 24 rCell.NumberFormat = "h:mm AM/PM" End If Next End Sub

The macro uses an integer division to determine the number of hours (iHours) and stuffs the remainder into iMins. This is then adjusted into a time value and placed back into the cell, which is then formatted as a time. You can change the cell format, if desired, to any of the other time formats supported by Excel.

*ExcelTips* is your source for cost-effective Microsoft Excel training. This tip (2775) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Converting Numeric Values to Times.

*Related Tips:*

**Create Custom Apps with VBA!** Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out *Mastering VBA for Office 2013* today!

@John

RIGHT(C2) as minute argument cannot work since it returns only one digit: 4 and 2 in your example and that's what Excel displays (it ignores trailing zeros after the period).

The following formula will solve your problem: =TIME(INT(C2),MOD(C2,1)*100,)

MOD(C2,1) ruturns the number after the period (0.4 and 0.2 in your example) then multiplying it by 100 results in 40 and 20. That are the correct minutes.

RIGHT(C2) as minute argument cannot work since it returns only one digit: 4 and 2 in your example and that's what Excel displays (it ignores trailing zeros after the period).

The following formula will solve your problem: =TIME(INT(C2),MOD(C2,1)*100,)

MOD(C2,1) ruturns the number after the period (0.4 and 0.2 in your example) then multiplying it by 100 results in 40 and 20. That are the correct minutes.

I have tried the formulas and tried to work with the H:mm and making the number a text. I have used the formula =TIME(LEFT(C2,LEN(C2)),RIGHT(C2),) but

The following occurs:

Start time End time

13.40 14.20

becomes

1:04 PM 2:02 PM

I am missing 36 minutes on start and 18 on finish

Help

The following occurs:

Start time End time

13.40 14.20

becomes

1:04 PM 2:02 PM

I am missing 36 minutes on start and 18 on finish

Help

I'm trying to combine 2 excel files containing times in seconds. Original file shows time format in 00:24.0. Additional files time format shows

00:00:18.208. When combined, I am unable to sort numerically. How do you change 00:00:18.208 to 00:00:18.0?

thanks

00:00:18.208. When combined, I am unable to sort numerically. How do you change 00:00:18.208 to 00:00:18.0?

thanks

I need to convert numbers into hh:mm:ss AM/PM and all the format options are not helping. The base numbers can be as few as 4 characters to as many as 6 characters. I have more than 2000 lines that I need to apply this format.

I do not understand where I am to enter the codes you mention in the comments if I paste into the cell that already contains that information I will lose the information. IS there not a solution in the menus?

I do not understand where I am to enter the codes you mention in the comments if I paste into the cell that already contains that information I will lose the information. IS there not a solution in the menus?

Micky,

You are a gem thank you.

kind regards

Deb

You are a gem thank you.

kind regards

Deb

@Deb Lloyd,

Maybe custom formatting the sum cell as: [h]:mm might help.

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Maybe custom formatting the sum cell as: [h]:mm might help.

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Hi Micky,

I have come up with one hitch, this method can only go up to 24 hours. the data I am collecting will exceed this, any ideas?

Many thanks

Deb

I have come up with one hitch, this method can only go up to 24 hours. the data I am collecting will exceed this, any ideas?

Many thanks

Deb

Hi Micky,

Many thanks for your response.

Many thanks for your response.

@Deb Lloyd,

Assuming your range is A1:A50 - try the following formula:

=SUM(A1:A50)/1440

and format the total cell as "Time".

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Assuming your range is A1:A50 - try the following formula:

=SUM(A1:A50)/1440

and format the total cell as "Time".

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

@Deb Lloyd,

Assuming your range is A1:A50 - try the following formula:

=SUM(A1:A50)/1440

and format the total cell as "Time".

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Assuming your range is A1:A50 - try the following formula:

=SUM(A1:A50)/1440

and format the total cell as "Time".

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Hi I have columns of time all in 5 minute blocks eg 15 minutes, 355 minutes. I need to total them at the bottom of the column to hours and minutes. How can this be done? TIA.

@Joanna Kruger,

You may also try Custom format on the original values:

##\:##\:##

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

You may also try Custom format on the original values:

##\:##\:##

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

@Joanna Kruger,

Try: =REPLACE(REPLACE(A1,2+(A1>99999),0,":"),5+(A1>99999),0,":")

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Try: =REPLACE(REPLACE(A1,2+(A1>99999),0,":"),5+(A1>99999),0,":")

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Hi Allen,

Sorry my earlier message may have been unclear.

I have data in the following format:

060000

062950

095959

182959

230000

232959

I would like to use the TIME function to convert this data into

6:00:00

6:29:50

9:59:59

18:29:59

23:00:00

23:29:59

Please could you help?

Sorry my earlier message may have been unclear.

I have data in the following format:

060000

062950

095959

182959

230000

232959

I would like to use the TIME function to convert this data into

6:00:00

6:29:50

9:59:59

18:29:59

23:00:00

23:29:59

Please could you help?

Hi This above TIME function is very helpful.

How I modify the formula for a 6 digit number?

I have data in this format:265959

and I would like to convert it to:

26:59:59 PM

How I modify the formula for a 6 digit number?

I have data in this format:265959

and I would like to convert it to:

26:59:59 PM

Thank you, very helpful!

hi my name is mikaylah white i am a very nice girl if u ask me any question i will be nice to answer i am like a angel to everybody

@Saran,

Try Custom Format such as: ##\:##

----------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Try Custom Format such as: ##\:##

----------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Hello,

I am just looking for a custom change in the same cell if I enter a number and convert to time.

Example: If I enter a value "0405" it should correct to the same format to "04:05" in same cell like the way used to get results on custom format like 0000, so the value "1" will auto convert into "0001"

Thank you

I am just looking for a custom change in the same cell if I enter a number and convert to time.

Example: If I enter a value "0405" it should correct to the same format to "04:05" in same cell like the way used to get results on custom format like 0000, so the value "1" will auto convert into "0001"

Thank you

@ Antonio

This is very easy as Excel stores times as a percentage of a day i.e. the time 12:00 is stored as 0.5, 06:00 is stored as 0.25.

Similarly, as Excel stores percentages as a decimal i.e. 50% is stored as 0.5.

If you have the percentage stored in cell A1 you'll probably have the cell formatted as a percentage, you could just change the formatting of the cell to a time format, or copy the contents to another cell (=A1) and use a time format for that cell.

To make it a text string use the formula =TEXT(A1,"hh:mm:ss")

This is very easy as Excel stores times as a percentage of a day i.e. the time 12:00 is stored as 0.5, 06:00 is stored as 0.25.

Similarly, as Excel stores percentages as a decimal i.e. 50% is stored as 0.5.

If you have the percentage stored in cell A1 you'll probably have the cell formatted as a percentage, you could just change the formatting of the cell to a time format, or copy the contents to another cell (=A1) and use a time format for that cell.

To make it a text string use the formula =TEXT(A1,"hh:mm:ss")

I'm trying to convert 100% to 24 periods, 100% = to 24 hours, 50% 12 hours... i.e. if I have 87.5%, how could I turn it into __ hours...

Thank you!!

Thank you!!

@Sam

You can use the formula:

=LEFT(A1,LEN(A1)-2)*12+RIGHT(A1,2)/5

This sums the number of hours by 12 and the number of minutes divided by 5.

Note: it assumes that the value in cell A1 has at least 3 digits. so care has to be taken when entering times in the hour after midnight entering 0015 is likely to be treated by Excel as a number and it will remove the leading zeroes leaving 15. The LEFT function returns "" which results in an error when multiplied by 12.

This is a more robust formula:

=IF(LEN(A1)>2,LEFT(A1,LEN(A1)-2),0)*12+RIGHT(A1,2)/5

The formula doesn't do any rounding to whole units of 5 minutes this is easily achieved by using the ROUND, ROUNDUP or ROUNDDOWN functions on the result depending on what the requirement is.

You can use the formula:

=LEFT(A1,LEN(A1)-2)*12+RIGHT(A1,2)/5

This sums the number of hours by 12 and the number of minutes divided by 5.

Note: it assumes that the value in cell A1 has at least 3 digits. so care has to be taken when entering times in the hour after midnight entering 0015 is likely to be treated by Excel as a number and it will remove the leading zeroes leaving 15. The LEFT function returns "" which results in an error when multiplied by 12.

This is a more robust formula:

=IF(LEN(A1)>2,LEFT(A1,LEN(A1)-2),0)*12+RIGHT(A1,2)/5

The formula doesn't do any rounding to whole units of 5 minutes this is easily achieved by using the ROUND, ROUNDUP or ROUNDDOWN functions on the result depending on what the requirement is.

I have created a spreadsheet where I would like to show a numerical field but also convert the number into a unit of time as well. So I would have 2 fields.

Eg - 1 unit = 5 minutes

Thank you.

Eg - 1 unit = 5 minutes

Thank you.

How do I apply this to only specific columns in a spreadsheet?

Sub NumberToTime()

Dim rCell As Range

Dim iHours As Integer

Dim iMins As Integer

For Each rCell In Selection

If IsNumeric(rCell.Value) And Len(rCell.Value) > 0 Then

iHours = rCell.Value 100

iMins = rCell.Value Mod 100

rCell.Value = (iHours + iMins / 60) / 24

rCell.NumberFormat = "h:mm AM/PM"

End If

Next

End Sub

Sub NumberToTime()

Dim rCell As Range

Dim iHours As Integer

Dim iMins As Integer

For Each rCell In Selection

If IsNumeric(rCell.Value) And Len(rCell.Value) > 0 Then

iHours = rCell.Value 100

iMins = rCell.Value Mod 100

rCell.Value = (iHours + iMins / 60) / 24

rCell.NumberFormat = "h:mm AM/PM"

End If

Next

End Sub