Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Formula for Adding Time

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Formula for Adding Time

Sorry about the mis-aligned text, it looked great in Notepad before I pasted
it iinto the info block.

"Rob" wrote:

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.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,726
Default Formula for Adding Time

You can simplify it to

=IF(OR(NOT(E142),NOT(E141)),SUM(E133:E142,F141)-SUM(F132:F141,E141),"")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rob" wrote in message
...
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.



  #4   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Formula for Adding Time

Thanks for the advice but I get the below with that formula..

0 days 0 hours 01 minutes 15.37%
0 days 0 hours 01 minutes 13.32%
0 days 0 hours 01 minutes 12.70%
0 days 0 hours 01 minutes 12.30%
0 days 0 hours 01 minutes 12.70%
0 days 0 hours 01 minutes 12.50%
0 days 0 hours 01 minutes 12.50%
0 days 0 hours 01 minutes 12.30%
0 days 0 hours 01 minutes 12.50%
#VALUE!
0 days 0 hours 01 minutes 12.50%
0 days 0 hours 08 minutes


"Bob Phillips" wrote:

You can simplify it to

=IF(OR(NOT(E142),NOT(E141)),SUM(E133:E142,F141)-SUM(F132:F141,E141),"")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rob" wrote in message
...
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.




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,726
Default Formula for Adding Time

It actually needs to check more explicitly as the ranges overlap

=IF(OR(E142<FALSE,E141<FALSE),SUM(E133:E142,F141 )-SUM(F132:F141,E141),"")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rob" wrote in message
...
Thanks for the advice but I get the below with that formula..

0 days 0 hours 01 minutes 15.37%
0 days 0 hours 01 minutes 13.32%
0 days 0 hours 01 minutes 12.70%
0 days 0 hours 01 minutes 12.30%
0 days 0 hours 01 minutes 12.70%
0 days 0 hours 01 minutes 12.50%
0 days 0 hours 01 minutes 12.50%
0 days 0 hours 01 minutes 12.30%
0 days 0 hours 01 minutes 12.50%
#VALUE!
0 days 0 hours 01 minutes 12.50%
0 days 0 hours 08 minutes


"Bob Phillips" wrote:

You can simplify it to

=IF(OR(NOT(E142),NOT(E141)),SUM(E133:E142,F141)-SUM(F132:F141,E141),"")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rob" wrote in message
...
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.








  #6   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Formula for Adding Time

That works Perfectly!!!!!!!!!!!

THANK YOU SOOOO MUCH!!!!!

Cheers!
Rob

"Bob Phillips" wrote:

It actually needs to check more explicitly as the ranges overlap

=IF(OR(E142<FALSE,E141<FALSE),SUM(E133:E142,F141 )-SUM(F132:F141,E141),"")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rob" wrote in message
...
Thanks for the advice but I get the below with that formula..

0 days 0 hours 01 minutes 15.37%
0 days 0 hours 01 minutes 13.32%
0 days 0 hours 01 minutes 12.70%
0 days 0 hours 01 minutes 12.30%
0 days 0 hours 01 minutes 12.70%
0 days 0 hours 01 minutes 12.50%
0 days 0 hours 01 minutes 12.50%
0 days 0 hours 01 minutes 12.30%
0 days 0 hours 01 minutes 12.50%
#VALUE!
0 days 0 hours 01 minutes 12.50%
0 days 0 hours 08 minutes


"Bob Phillips" wrote:

You can simplify it to

=IF(OR(NOT(E142),NOT(E141)),SUM(E133:E142,F141)-SUM(F132:F141,E141),"")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rob" wrote in message
...
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.






  #7   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Formula for Adding Time

One more Question If you would please??

How would that be done using the VB for a command button?

Rob

"Bob Phillips" wrote:

It actually needs to check more explicitly as the ranges overlap

=IF(OR(E142<FALSE,E141<FALSE),SUM(E133:E142,F141 )-SUM(F132:F141,E141),"")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rob" wrote in message
...
Thanks for the advice but I get the below with that formula..

0 days 0 hours 01 minutes 15.37%
0 days 0 hours 01 minutes 13.32%
0 days 0 hours 01 minutes 12.70%
0 days 0 hours 01 minutes 12.30%
0 days 0 hours 01 minutes 12.70%
0 days 0 hours 01 minutes 12.50%
0 days 0 hours 01 minutes 12.50%
0 days 0 hours 01 minutes 12.30%
0 days 0 hours 01 minutes 12.50%
#VALUE!
0 days 0 hours 01 minutes 12.50%
0 days 0 hours 08 minutes


"Bob Phillips" wrote:

You can simplify it to

=IF(OR(NOT(E142),NOT(E141)),SUM(E133:E142,F141)-SUM(F132:F141,E141),"")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rob" wrote in message
...
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.






  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,726
Default Formula for Adding Time

Depends where you are in relation to cells E142 etc. Is it irrelevant, that
is it will always be E142, or is it the same row, row above, ten cells left
etc.?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rob" wrote in message
...
One more Question If you would please??

How would that be done using the VB for a command button?

Rob

"Bob Phillips" wrote:

It actually needs to check more explicitly as the ranges overlap

=IF(OR(E142<FALSE,E141<FALSE),SUM(E133:E142,F141 )-SUM(F132:F141,E141),"")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rob" wrote in message
...
Thanks for the advice but I get the below with that formula..

0 days 0 hours 01 minutes 15.37%
0 days 0 hours 01 minutes 13.32%
0 days 0 hours 01 minutes 12.70%
0 days 0 hours 01 minutes 12.30%
0 days 0 hours 01 minutes 12.70%
0 days 0 hours 01 minutes 12.50%
0 days 0 hours 01 minutes 12.50%
0 days 0 hours 01 minutes 12.30%
0 days 0 hours 01 minutes 12.50%
#VALUE!
0 days 0 hours 01 minutes 12.50%
0 days 0 hours 08 minutes


"Bob Phillips" wrote:

You can simplify it to

=IF(OR(NOT(E142),NOT(E141)),SUM(E133:E142,F141)-SUM(F132:F141,E141),"")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rob" wrote in message
...
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.








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
can excel calculate time increments in a formula? lmunzen Excel Worksheet Functions 2 September 11th 06 09:39 PM
formula to determine time range overlap? William DeLeo Excel Discussion (Misc queries) 0 June 6th 06 08:26 PM
Help with time formula so the time will not change. Joker Excel Discussion (Misc queries) 1 February 17th 06 10:04 AM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 06:18 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM


All times are GMT +1. The time now is 01:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"