Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a series of IDs that are represented in a column with the [h]:mm:ss format in excel. I want to extract the [h] portion that precedes the colon and have it stored as a number. The problem I run into is that it converts the digits to a new number, not the digits as displayed.

Example: 1234:00:00 formatted as [h]:mm:ss Desired text in new column: 1234

I am looking to use a straight formula to solve.

share|improve this question

migrated from stackoverflow.com May 29 '13 at 17:43

This question came from our site for professional and enthusiast programmers.

1  
What language? What is "it" (that converts the digits to a new number)? –  emd May 29 '13 at 16:07
    
If I convert the 1234:00:00 to a Number, the cell becomes 51.42. I am able to display 1234 if I format only as [h] but that is not searchable when trying to compare columns. I can't figure out how to both display 1234 in the cell as well as it actually be 1234, the number. –  user2433269 May 29 '13 at 16:10

4 Answers 4

up vote 5 down vote accepted

You have two options here.

If you want to simply display the hours, you can just copy and paste the cells (or a simple =B5 formula) and give it a custom display format of [h].

If you need to use the number for other calculations, then you can use the following formula:

=TRUNC(B5*24,0)

And if you want to get a little fancier, using excel date/time methods, you can use:

=DATEDIF(0, B5,"D") * 24 + HOUR(B5)
share|improve this answer
1  
That does the trick. You just saved me hours. Thank you. –  user2433269 May 29 '13 at 16:20
    
+1 for TRUNC formula (you could also use INT) - don't use DAYS360 here it doesn't give an accurate day count so, for example, it will convert 8888:00:00 to 8768 –  barry houdini May 29 '13 at 23:26
    
@barryhoudini: Thanks, I didn't realize that there was an issue with DAYS360 (I thought that's what it was for, lol). Second example removed. –  lnafziger May 30 '13 at 0:08
    
DAYS360 treats each year as if it has 360 days, i.e. 30 each month so it may not be accurate across months - in Excel 2013 there is a new DAYS function which does give an accurate count, in earlier versions you can use DATEDIF or if no times are involved just a simple subtraction. –  barry houdini May 30 '13 at 10:37
    
Great, thanks for the info! –  lnafziger Jun 2 '13 at 19:14

Things like =HOUR() will not do the trick since it will not include bits that spill over into day periods. Ditto =TEXT(A1, "hh") * 1. 1234 hours spills over into a number of days.

You need to do this:

=ROUNDDOWN(VALUE(D5)*24,0)

Which will include whole days in the total number of hours. Rounding strips the minutes etc.

This works because Excel represents time as numbers which increase by 1 each day.

The VALUE call circumvents Excel's nasty habit of propagating time formats when editing untouched cells. You may not need it in later Excel versions.

share|improve this answer
    
+1 Yup, I would have originally gone for copy/paste to a txt then import as txt file, but the habit of using text() has been growing on me for the past few weeks, heh xD. Using notepad for assistance is still a favourite of me! –  Jerry May 29 '13 at 16:30
    
TEXT function will work if you use it like this =TEXT(A1,"[h]")*1 - format result cell as general –  barry houdini May 29 '13 at 23:23

If you're working in a sheet formula, use the hour function:

enter image description here

If you're using VBA, similar solution:

MsgBox Hour(sheet.Cells(1, 1))
share|improve this answer
1  
That works if the hour is actually an hour but doesn't with the numbers I'm using, which are actually IDs. 1234:00:00 with the formula above gives the output of 10, not 1234. :-/ –  user2433269 May 29 '13 at 16:15

If your numbers were stored as text instead of times, you could have used this simple array formula to extract the first continuous numeric sequence from the text:

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$14),1)),0),MATCH(FALSE,ISNUMBER(1*MID(A1,ROW(OFFSET($1:$14,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$14),1)),0)-1,)),1)),0)-1)

note: press CTRl+SHIFT+ENTER when entering as it is an array formula

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.