Converting Numeric Values to Times

Hi Micky,

I want to convert the below 8 digit numbers in the time format (HH:MM:SS AM/PM)

Number Time(HH:MM:SS AM/PM)
------------- ------------------------
21465542 9:46:55 PM

21581198 9:58:11 PM

Please can you help with this query?

I have a date that has been downloaded from an electronic health record that shows up as mm/dd/yyyy 0000. I can separate them in text to column but when I convert 0000 to time it shows as 00:00. for example 0917 shows up as 00:00. It is in military time so even the 1530 shows as 00:00.

So, 01/01/2018 0055 (cell A1)becomes 1/1/2018 (ends up in cell B1) and 55 (in cell C1) when I use text to column.

Disch Date/Time Disch Date/ Time
01/01/2018 0055 1/1/2018 55 FALSE

The false is the formula ISTEXT.

I have a date that has been downloaded from an electronic health record that shows up as mm/dd/yyyy 0000. I can separate them in text to column but when I convert 0000 to time it shows as 00:00. for example 0917 shows up as 00:00. It is in military time so even the 1530 shows as 00:00.

So, 01/01/2018 0055 (cell A1)becomes 1/1/2018 (ends up in cell B1) and 55 (in cell C1) when I use text to column.

@Al

If your data always have 4 digits, this formula will do the job:

=TIME(INT(LEFT(A1,2)/60),MOD(LEFT(A1,2),60),RIGHT(A1,2))

You can use data with leading zero(s) but then the cells containing them must be formatted as text or the entry must be started with an apostrophe.

Hi,
I have a set of data which represent time in minutes and seconds ex.7112 which represents 71 minutes and 12 seconds. Or as another example, 4113 which represents 41 minutes and 13 seconds, these numbers are recognized as numbers only and not hr,mm,ss with the appropriate colon in-between
Is there a formula that will help me convert as an example these numbers 7112 to 1:11:12 or the 4113 to 0:41:13?

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

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

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

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?

Micky,
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

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

Hi Micky,
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

@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

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

@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

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?

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

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

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

@ 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")

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

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

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.

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