I have a time tracking report run by a program other than Excel. I
copy the data and paste it into Excel.

What I get is the amount of time spent in various conditions.
Simplified to 4 columns for this example, A B C D:

27:57:40 :03:13 19:38:04 1:48:57

All of these end up in different formats.
[h]:mm:ss - 1/1/1900 3:57:40 AM for the data in A1
General - :03:13 in B1
hh:mm:ss - 7:38:04 PM in C1
General - 1:48:57 in D1

First things first - it is the program I am copying from that uses the
format :00:00 for anything that has a 0 in the hour location.

How can I get a 0 into that hour location to display 0:03:13 for my
example?

This is the formula that I came up with:
IF(LEFT(B1,SEARCH(":",B1))=":","0"&B1,B1.

This works if true, but it fails to display B1 if false. I have to
use the TEXT formula on B1 to make it display correctly if false. I
can do that but I would have to insert 2 columns for each column of
data so I could do the TEXT Formula and then the IF formula. All this
I can do in VB.

Am I missing a simpler process/formula/VB solution for this?

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?

Ultimately the data that I want is A1 -B1 -C1 -D1. I considered
changing everything to decimal but I still run into some of the same
problems.

Please let me know any insights you might have. I am sure I am
missing something simple.

Thank you for the help.