Convert [h]:mm to decimal hours

**edgarrm** · 08-15-2012, 12:54 AM

Hello all. I'm using the method I found in this forum to calculate the time between 2 dates & time - http://www.excelforum.com/excel-form...=1#post1982655

Didn't think it'd be that simple

Now that I have the value in [h]:mm, how do I convert it into decimal hours?

Thanks in advance.

**FDibbins** · 08-15-2012, 12:58 AM

try using format special 00.00.00

**edgarrm** · 08-15-2012, 01:10 AM

Hi FDibbins, I did that but it didn't work. I was thinking to do a string tokenizer method since the number format is like 440:06:15. It'd extract "440" as the hour and "06" divided by 60 into decimal, then put those two together. Is that possible?

EDIT: It would show 440.1 decimal hours.

EDIT [SOLUTION]:

I solved it. A bit long winded but it works.

I convert the value to text with =TEXT(E1,"[h]:mm"). Then, using =LEFT(E1,SEARCH(":",E1)-1)+ MID(E1,SEARCH(":",E1)+1,20)/60 I now have the value in decimal hours

**FDibbins** · 08-15-2012, 01:44 AM

440:06:15 actually looks like 1/18/1900 8:06:15 AM when you look at it in the formula bar. try this tho. with your time in A1, =DAY(A1)*24+HOUR(A1)+MINUTE(A1)/60

let me know how that works for you?

**edgarrm** · 08-15-2012, 02:35 AM

Originally Posted by FDibbins

440:06:15 actually looks like 1/18/1900 8:06:15 AM when you look at it in the formula bar. try this tho. with your time in A1, =DAY(A1)*24+HOUR(A1)+MINUTE(A1)/60

let me know how that works for you?

It works too!

And it's less steps too. Thank you very much.