I recently wanted (needed?) to find out the delta between ‘actual’ start times and ‘requested’ or ‘scheduled’ start times of Change orders.
We use CA Service Desk 12.5 where I am right now. The output of the data looked liked such:
Scheduled Start Date
12/2/2011 6:15:00 PM
Actual Start date
12/2/2011 6:21:58 PM
Let’s say that was cell A2 and cell B2.
The first thing I did was: =(B2-A2*86400). This produces the raw number of seconds. In this case 418 seconds.
I called this “Total Seconds”
In the next cell I did this: =ROUNDDOWN(C2/60,0)
This will produce the number “6” which is the “Total Minutes”
In the next cell I used this: =ABS(D2)
This will produce the number “6” which is the “Absolute Minutes” – this is important if/when we deal with negative numbers which can happen when people start changes before their scheduled start time.
The next cell I used was this: =ROUNDDOWN(D2/60,0)
This produced the number “0” in this case. This captures “Total Hours” – this will produce a number if the delta is greater than 60 minutes or 3600 seconds.
The next cell I used was this: =ABS(F2)
Which will show the “Absolute Hours”
The next cell I use was: =ABS(C2)
Which will show the “Absolute Seconds”
Now we basically have all the raw numbers and converted absolute numbers we need to produce values that will show negative or positive time deltas with only 1 negative sign showing up in the final number.
Now the more ‘complicated’ calculations begin.
In the next cell I use this formula: =IF((H2>3600),(H2-(3600*G2)), IF((H2<3600),H2, H2))
This will produce the total number of ‘seconds’ that are left over after all ‘hours’ have been accounted for. In this case it is just 418 seconds. However, if our initial times were say..8001 seconds apart, then we would have over 2 hours of delta and this formula will have produced “801” seconds.
In the next cell I use this formula: =IF((E2>60),(E2-(60*G2)), IF((D2<60),D2, D2))
Similar to the previous cell, this one calculates all minutes left over after all hours have been accounted for. This will also place the negative sign at the minute mark if appropriate.
In the next cell I use this formula: =IF((I2>60),(MOD(I2,60)), IF((C2<0),C2,I2))
Again, this determines the total left over seconds after all the minutes have been accounted for. It will also put the negative sign at the second mark if appropriate.
Next I use this formula: =F2&”:”&J2&”:”&INT(K2)
Which will produce the “Math Time” or “Raw Time” and in our case it should look like this:
Finally, I use this formula: =TEXT(L2, “h:mm:ss”)
Which will display the “Display Time” and that will look like this:
If we had -418 instead of 418 the final two sets of numbers would look like this:
So, not ‘perfect’, but it will put the negative sign at the appropriate level.
I have run many tests against this and it seems to work for any reasonable number I can think of or have seen in the reports.
There may be a more straightforward way of doing this. I must admit I became tired at some point and may have overlooked an easier path. I tried many different approaches but this is the only one I have found that deals well with negative as well as positive numbers and both large and small numbers.