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.

2
  • 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)
4
  • 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.

2
  • +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
  • 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 agree to our terms of service, privacy policy and cookie policy