| Home |
| Search |
| Today's Posts |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| can excel calculate time increments in a formula? | Excel Worksheet Functions | |||
| formula to determine time range overlap? | Excel Discussion (Misc queries) | |||
| Help with time formula so the time will not change. | Excel Discussion (Misc queries) | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Threaded Mode
