[Rob]

Hello,
I'm trying to do three things here...

1: Add the total time that someone had a document in their possestion for
review (individually)
2: Add the total time for row 141 Plus the inbetween time of all the other
times from when rcvd to sent
3: Add all the time put together


Below is the formula I am using but I have issues with it, especially when I
try to convert it to VB so That I can insert row 141's formula automatically.


Cell Formula

=IF(E142<FALSE,SUM((E133-F132)+(E134-F133)+(E135-F134)+(E136-F135)+(E137-F136)+(E138-F137)+(E139-F138)+(E140-F139)+(E141-F140)+(F141-E141)+(E142-F141)+(F141-E141)),IF(E141<FALSE,SUM((E133-F132)+(E134-F133)+(E135-F134)+(E136-F135)+(E137-F136)+(E138-F137)+(E139-F138)+(E140-F139)+(E141-F140)+(F141-E141)+(E142-F141)),""))


GOOD Results

COL "E" COL "F" COL"G" - "J" COL"K" COL"L"

Sent Rcvd

R132 01/07/2007 12:00 PM 01/07/2007 12:02 PM 0 days 0 hours 01 minutes 6.11%
R133 01/07/2007 12:03 PM 01/07/2007 12:04 PM 0 days 0 hours 01 minutes 5.29%
R134 01/07/2007 12:05 PM 01/07/2007 12:06 PM 0 days 0 hours 01 minutes 5.05%
R135 01/07/2007 12:07 PM 01/07/2007 12:08 PM 0 days 0 hours 01 minutes 4.89%
R136 01/07/2007 12:09 PM 01/07/2007 12:10 PM 0 days 0 hours 01 minutes 5.05%
R137 01/07/2007 12:11 PM 01/07/2007 12:12 PM 0 days 0 hours 01 minutes 4.97%
R138 01/07/2007 12:13 PM 01/07/2007 12:14 PM 0 days 0 hours 01 minutes 4.97%
R139 01/07/2007 12:15 PM 01/07/2007 12:16 PM 0 days 0 hours 01 minutes 4.89%
R140 01/07/2007 12:17 PM 01/07/2007 12:18 PM 0 days 0 hours 01 minutes 4.97%
R141 01/07/2007 12:19 PM 01/07/2007 12:20 PM 0 days 0 hours 12 minutes 60.26%
R142 01/07/2007 12:21 PM 01/07/2007 12:22 PM 0 days 0 hours 01 minutes 4.97%
R143 TOTALS: 0 days 0 hours 20 minutes


GOOD Results

Sent Rcvd

01/07/2007 12:00 PM 01/07/2007 12:02 PM 0 days 0 hours 01 minutes 6.11%
01/07/2007 12:03 PM 01/07/2007 12:04 PM 0 days 0 hours 01 minutes 5.29%
01/07/2007 12:05 PM 01/07/2007 12:06 PM 0 days 0 hours 01 minutes 5.05%
01/07/2007 12:07 PM 01/07/2007 12:08 PM 0 days 0 hours 01 minutes 4.89%
01/07/2007 12:09 PM 01/07/2007 12:10 PM 0 days 0 hours 01 minutes 5.05%
01/07/2007 12:11 PM 01/07/2007 12:12 PM 0 days 0 hours 01 minutes 4.97%
01/07/2007 12:13 PM 01/07/2007 12:14 PM 0 days 0 hours 01 minutes 4.97%
01/07/2007 12:15 PM 01/07/2007 12:16 PM 0 days 0 hours 01 minutes 4.89%
01/07/2007 12:17 PM 01/07/2007 12:18 PM 0 days 0 hours 01 minutes 4.97%
01/07/2007 12:19 PM 01/07/2007 12:20 PM 0 days 0 hours 12 minutes 60.26%
01/07/2007 12:21 PM
TOTALS: 0 days 0 hours 20 minutes


BAD Results

Sent Rcvd

01/07/2007 12:00 PM 01/07/2007 12:02 PM 0 days 0 hours 01 minutes 6.11%
01/07/2007 12:03 PM 01/07/2007 12:04 PM 0 days 0 hours 01 minutes 5.29%
01/07/2007 12:05 PM 01/07/2007 12:06 PM 0 days 0 hours 01 minutes 5.05%
01/07/2007 12:07 PM 01/07/2007 12:08 PM 0 days 0 hours 01 minutes 4.89%
01/07/2007 12:09 PM 01/07/2007 12:10 PM 0 days 0 hours 01 minutes 5.05%
01/07/2007 12:11 PM 01/07/2007 12:12 PM 0 days 0 hours 01 minutes 4.97%
01/07/2007 12:13 PM 01/07/2007 12:14 PM 0 days 0 hours 01 minutes 4.97%
01/07/2007 12:15 PM 01/07/2007 12:16 PM 0 days 0 hours 01 minutes 4.89%
01/07/2007 12:17 PM 01/07/2007 12:18 PM 0 days 0 hours 01 minutes 4.97%
01/07/2007 12:19 PM 01/07/2007 12:20 PM ######################### 60.26%

TOTALS: #########################





Convert Formula to VB for Inserting formula automatically by a command button

Range("K" & RowID + 9).FormulaR1C1 =
"=IF(RC[-5]<FALSE,(RC[-6]-R[-1]C[-5])+(R[-1]C[-6]-R[-2]C[-5])+(R[-2]C[-6]-R[-3]C[-5])+(R[-3]C[-6]-R[-4]C[-5])+(R[-4]C[-6]-R[-5]C[-5])+(R[-5]C[-6]-R[-6]-C[-5])+(R[-6]C[-6]-R[-7]C[-5])+(R[-7]C[-6]-R[-8]C[-5])+(R[-8]C[-6]-R[-9]C[-5])+(RC[-5]-RC[-6]),""""))"



HORRIBLE Results!!

01/07/2007 12:00 PM 01/07/2007 12:02 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:03 PM 01/07/2007 12:04 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:05 PM 01/07/2007 12:06 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:07 PM 01/07/2007 12:08 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:09 PM 01/07/2007 12:10 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:11 PM 01/07/2007 12:12 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:13 PM 01/07/2007 12:14 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:15 PM 01/07/2007 12:16 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:17 PM 01/07/2007 12:18 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:19 PM 01/07/2007 12:20 PM 7 days 11 hours 56 minutes 100.00%
01/07/2007 12:21 PM 01/07/2007 12:22 PM 0 days 0 hours 01 minutes 0.00%
TOTALS: 7 days 12 hours 05 minutes



But No "######" Signs when a couple of lines are missing.

0 days 0 hours 01 minutes 0.00%
01/07/2007 12:00 PM 01/07/2007 12:02 PM
One 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:05 PM 01/07/2007 12:06 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:07 PM 01/07/2007 12:08 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:09 PM 01/07/2007 12:10 PM
0 days 0 hours 01 minutes 0.00%
01/07/2007 12:13 PM 01/07/2007 12:14 PM
0 days 0 hours 01 minutes 0.00%
01/07/2007 12:17 PM 01/07/2007 12:18 PM 7 days 11 hours 59 minutes 100.00%
01/07/2007 12:19 PM 01/07/2007 12:20 PM
TOTALS: 7 days 12 hours 05 minutes



NOT GOOD Either
0 days 0 hours 01 minutes #VALUE!
01/07/2007 12:00 PM 01/07/2007 12:02 PM 0 days 0 hours 01 minutes #VALUE!
01/07/2007 12:03 PM 01/07/2007 12:04 PM

0 days 0 hours 01 minutes #VALUE!
01/07/2007 12:09 PM 01/07/2007 12:10 PM


0 days 0 hours 01 minutes #VALUE!
01/07/2007 12:17 PM 01/07/2007 12:18 PM #VALUE! #VALUE!
01/07/2007 12:19 PM 01/07/2007 12:20 PM
TOTALS: #VALUE!



Can someone please tell me how I can fix this because it's starting to drive
me crazy?

Thank You So Very Much In Advance!
Cheers,
Rob


P.S.
Sorry for the information overload.