Strange time format [AMOS hours] , need to subtract!

Use this in C2:

=VALUE(LEFT(A2,FIND(":",A2)-1))-B2& RIGHT(A2,3)

So much to go into here...

Lets keep it short and Simple KISS

You have TEXT in the fields right now. You should be presenting this as a whole value. Decimal

How would you do this?

Glad you asked. The Hours are hours so leave them be but convert the 22 minutes to a decimal of time. Minutes/60, so 22/60 = .367

Now we have a VALUE not TEXT!

Take the Value of 55216.367 and place it into A2

Divide that value by 24, because you want to see it in a worldly view of time HH:MM:SS... only you want Cumulative so [h]:mm:ss and really you just want [h]:mm

When we divide 55216.367 by 24 we get 2342.35 now we have a real time value in the worldly view of time! so lets format the cell to VISUALLY represent it exactly as you started

with A2 selected, hit CTRL + 1 OR Right Click on A2 and select Format Cells from the menu (Should be towards the bottom, just read)

Now Choose CUSTOM

In the type field put the [h]:mm and hit okay...

Phew, we are back to where you began... but how do you remove a thousand hours!!!?

Simple enough, remember that you need to reduce 1k down to a time value so when we subtract from A2 we will need to say A2-(B2/24) and of course format C to have that same custom format for visual aide!


-----------------

So what does this look like on your actual file?

If I had to guess you have a list of values... I would first insert a column next to it and do text to column delimited at your COLON so that it splits hours from minutes
Then I would say (Hours + (Minutes/60) )/24 and format the resulting column to the format cited above so your back to square one and then add your Results - (Change/24) to get results in a list

So maybe not KISS like the previous suggestion of parsing and subtracting but if you want values that you can aggregate later you will need to go through this process and understand what is happening and why you could not just do -1k

The error is because the value in column A is a text string. In essence, they are numbers (time can be just a number in Excel) stored as text, but Excel seems unwilling to automatically recognize that many elapsed hours.

How much analysis needs to be done on this? If this is all the analysis, then leaving the inputs as text and the outputs as text (like Bernie Deitrick does) might be just fine. However, if there is additional analysis to perform on these numbers, I would be inclined to permanently convert the inputs to numbers, then any further analysis will be simpler.

An example of what I might do so that inputs and outputs are numbers rather than text:

1) Convert column A from text to number of days represented by that elapsed hour:minute text. Maybe in D2 =CONVERT(LEFT(A2,5),"hr","day")+CONVERT(RIGHT(A2,2),"mn","day") [format as elapsed hour such as [h]:mm or similar, if it is necessary to see in elapsed hour format]
2) Convert column B from hours to days. Maybe in E2 =CONVERT(B2,"hr","day") [again, format as elapsed hours if desired]
3) Column C is then simply =D2-E2 (formatted as elapsed hours, if desired).

It all depends on whether you are content to store the inputs and outputs as text or not. If you prefer Bernie's text output, you can ignore mine. But, if there is further analysis, it will almost certainly be easier to convert all values to numbers in order to facilitate the additional analyses.