excel - return time since last occurrence of a string for certain days of the week only -

-

i working on scheduling sheet. want calculate distance in weeks since person last scheduled on 1 of 3 different 'jobs'. want @ time since last scheduled on weekend, , individuals may scheduled on weekdays intervening between last weekends.

for example:

date    day_of_week task_a  task_b  task_c  distance_a  distance_b  distance_c 7/1/2015    wednesday   ed      mary    amy     0   0   0 7/2/2015    thursday    bill    judy    bob     0   0   0 7/3/2015    friday      ed      mary    amy     0   0   0 7/4/2015    saturday    ed      mary    amy     0   0   0 7/5/2015    sunday      ed      mary    amy     0   0   0 7/6/2015    monday      bill    mary    bob     0   0   0 7/7/2015    tuesday     ed      judy    amy     0   0   0 7/8/2015    wednesday   ed      amy     bob     0   0   0 7/9/2015    thursday    bob     ed      judy    0   0   0 7/10/2015   friday      ed      bob     judy    0   0   0 7/11/2015   saturday    ed      bob     judy    7   0   0 7/12/2015   sunday      ed      bob     amy     7   0   7

gives correct distances each of 3 tasks labels cells first followed new line of data @ each date.

for distance have attempted: 

{=if(and(b3="saturday", (a3-max(if($c$2:e2=c3, $a$2:a2, 0)))/7 <53),  (a3-max(if($c$2:e2=c3, $a$2:a2, 0)))/7, ".")}

which returns value on each saturday (as intended), cannot scan past recent weekday assignment, giving falsely low values. have attempted other if(or) & if(and) statements first failure generates false value ceasing program.

any assistance code , formatting example .csv or tab seperated values appreciated.

ok recap need calculate distance in weeks current date date of previous job scheduled @ weekend. conditions are:-

current date must fall on weekend previous date must on weekend previous date must not on same weekend current date must same person.

i've found easier use weekday function check if dates fall on weekend, putting get:-

=if(weekday($a3,16)>=3,"",     if(max($a$2:$a2*(c$2:c2=c3)*(weekday($a$2:$a2,16)<3)*(($a3-$a$2:$a2)>1))=0,"",       int(($a3-max($a$2:$a2*(c$2:c2=c3)*(weekday($a$2:$a2,16)<3)*(($a3-$a$2:$a2)>1))+1)/7)     )   )

to entered array formula in row 3 using ctrl shift enter

i think general logic of formula ok, or , and functions don't work in array formulas have replaced ands multiplications.

at moment checks person appearing in same column post version if needs check across 3 columns.

enter image description here


Comments

Post a Comment

Popular posts from this blog

python - How to create jsonb index using GIN on SQLAlchemy? -

-
here's current code creating index jsonb. index("mytable_data_idx_id_key", mytable.data['id'].astext, postgresql_using='gin') but got error. sqlalchemy.exc.programmingerror: (psycopg2.programmingerror) data type text has no default operator class access method "gin" hint: must specify operator class index or define default operator class data type. [sql: "create index event_data_idx_id_key on event using gin ((data ->> 'id'))"] is there way create index on sqlalchemy? the postgresql specific sqlalchemy docs @ http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#operator-classes mention postgresql_ops dictionary provide "operator class" used postgresql, , provide example illustrating use: index('my_index', my_table.c.id, my_table.c.data, postgresql_ops={ 'data': 'text_pattern_ops', ...
Read more

PHP DOM loadHTML() method unusual warning -

-
i had following code works fine on localhost: $document = new domdocument(); $document->loadhtml($p_result); $form = $document->getelementsbytagname('form')->item(0); // code continues using $form variable after same code updated on outside server, loadhtml() failed , issued warning. warning: domdocument::loadhtml() expects parameter 1 valid path, string given in path/name/to/script.php returned null instead of object code pretty gets fatal error. note contents of $p_result absolutely same on outside server , on localhost. but why displays kind of warning , why not work? doesn't loadhtml() expects argument 1 string in first place? why method expects parameter 1 valid path ? just make clear i'm not calling loadhtmlfile() , i'm calling loadhtml() . thanks. you're affected one of php bugs . issue present in php 5.6.8 , 5.6.9. have affected php version on server, , bug-free version on localhost. the bug forbids null ...
Read more

c# - TransactionScope not rolling back although no complete() is called -

-
i'm using transactionscope rollback transaction fail bool errorreported = false; action<importerrorlog> newerrorcallback = e => { errorreported = true; errorcallback(e); }; using (var transaction = new transactionscope()) { foreach (importtaskdefinition task in taskdefinition) { loader.load(streamfile, newerrorcallback, task.destinationtable, processingtaskid); } if (!errorreported) transaction.complete(); } i'm sure there no transactionscope started ahead or after code. i'm using entity framework insert in db. regardless state of errorreported transaction never rolled in case of error. what missing ? transactionscope sets transaction.current . that's does. wants transacted must @ property. i believe ef each time connection opened reason. probably, connection open when scope installed. open connection inside of scope or enlist manually. ef has nasty "design decision": default opens ...
Read more