Did some work on it and found a solution that works for all the
contingencies I was running into. Used all the suggestions I got and
was able to work it out. Thank you again everyone who helped.

=IF(ISNONTEXT(A1),A1,TIMEVALUE(TEXT(IF(LEFT(A1,SEA RCH(":",A1,1))
=":","0"&A1,A1),"[h]:mm:ss")))


On Mar 18, 12:42*pm, Dow wrote:
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.- Hide quoted text -

- Show quoted text -