I did not think of using ISNONTEXT. That works.
I tried the Timevalue before I posted. I found it in one of the other
Threads. Unfortunately I get a #Value error when I try it.
On Mar 17, 1:55*pm, jasontferrell wrote:
Am I missing a simpler process/formula/VB solution for this?
I think what could be happening is that the format for the cell with
the formula is set for general. *When it displays the text of the
result, it looks okay, but it's just text. *Maybe try setting the
format to an h:mm:ss format and use this formula:
=IF(ISNONTEXT(B1),B1,IF(LEFT(B1,1)=":",TIMEVALUE(" 0"&B1),B1))
So we get those numbers fixed. *Now in my example I can take A1 and
subtract B1. *I can also subtract C1 because it is already in a
compatible format. *D1 on the other hand is in General format and even
when I try changing the format manually to [h]:mm:ss I cannot subtract
it from A1. *I do not understand why it will not work.
Any thoughts here?
Try using the =Timevalue(D1) function.