0

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.

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
5

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)
  • 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
1

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.

  • +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
0

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))
  • 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
0

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.