SharePoint Calculated Columns :: Start and End Times

My custom CT (content type) is called Process Step. (The parent CT is Issue.) The purpose of the list is to allow loading of a series of steps (the same set for each month) and track the time it takes to complete each step, focusing on those identified as "key steps."
As I said, I added "start time," "end time" and "duration" to my content type. Start time is a date/time field which populates off a WF that uses "set list item" action to drop the date/time corresponding to "modified" which occurs when the item status changes from "not started" to "in progress." The same thing happens when status changes from "in progress" to "completed" - another WF updates "end time" to copy over whatever was the "modified" date/time matching that change.
"Duration" is a calculated column derived from end time minus start time, single line of text type, using this formula:
=TEXT([End time]-[Start time],"h:mm:ss")
The result of the calculation appears in the hours:minutes:seconds format
I want to provide a way to measure the duration against a target duration; therefore, the values I enter for each step as my target duration must also be in the hours:minutes:seconds format- correct?
Assuming that can be created, I then want one more calculated column )"duration comparison") which will compare the actual duration with the target duration. I am fuzzy on how to display this, however... I'm not sure what the best comparison format would be (a difference, a ratio, not sure). So I don't know what type of field to use. I think it will depend on how the target duration field is formatted.
Finally, I will create a view to display only the "key steps" and set up a KPI that lets me see the key steps' duration comparison values.
Is that the info you need?


Ok, starting to make sense now.
Everything sounds good so far...the "Duration" column should be returning a value in the "h:mm:ss" format (which it sounds like it is).
So, to add in a "Target Duration", you can just create a new "Single line of text" column and literally enter in the values in the same "h:mm:ss" format (i.e. 2.5 hrs would be 2:30:00). Do this for each step you want to track. (Q: are there any issues with manually entering in this data?)
For the "Duration Comparison" column, just use a Calculated column with a return type of "Single line of text" and a formula something like:
=IF(TEXT([Actual Duration],"HH:MM:SS")<TEXT([Target Duration],"HH:MM:SS"),TEXT([Target Duration]-[Actual Duration],"HH:MM:SS")&" Under",TEXT([Actual Duration]-[Target Duration],"HH:MM:SS")&" Over")
This formula would be read as: "If the text of the 'Actual Duration' column formatted as a standard 'hh:mm:ss' time format is less than the text of the 'Target Duration' column formatted as a standard 'hh:mm:ss' time format, subtract the 'Actual Duration' column from the 'Target Duration' column formatted as a standard 'hh:mm:ss' time format, followed by the text 'Under'. If not, subtract the 'Target Duration' column from the 'Actual Duration' column formatted as a standard 'hh:mm:ss' time format, followed by the text 'Over'."
This would display (using example data):
Start Time: 9:00:00
End Time: 9:45:00
Actual Duration: 00:45:00
Target Duration: 1:30:00
Duration Comparison: 00:45:00 Under
Getting a bit trickier, you could use an alternate formula of:

=IF(TEXT([Actual Duration],"HH:MM:SS")<TEXT([Target Duration],"HH:MM:SS"),HOUR([Target Duration]-[Actual Duration])&" hrs "&MINUTE([Target Duration]-[Actual Duration])&" min "&SECOND([Target Duration]-[Actual Duration])&" sec Under",HOUR([Actual Duration]-[Target Duration])&" hrs "&MINUTE([Actual Duration]-[Target Duration])&" min "&SECOND([Actual Duration]-[Target Duration])&" sec Over")
This would display it as:
Start Time: 9:00:00
End Time: 9:45:00
Actual Duration: 00:45:00
Target Duration: 1:30:00
Duration Comparison: 0 hrs 45 min 0 sec Under
This one parses out the individual hours/minutes/seconds into a more "textual" display rather than the "hh:mm:ss" style display, but either version would work.
The custom view portion should be pretty straight-forward, but does this help with the rest?