yogi_Conditionally Format Cells B3:3 If The End Date (in cell B3) Fell Within Weeks 1 To 52 of The Year of The End Date

Google Spreadsheet   Post  #2141

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-01-2017

question by LobsterSlapper:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/1EmzRio1SNw;context-place=forum/docs
Week-based Gantt Chart:solution for "shared" weeks?

Does anyone have suggestions for how to handle "shared" weeks, for a weekly Gantt Chart? 

My current spreadsheet uses conditional formatting (=WEEKNUM($A3)>=(B$2)) and cells based on week numbers, to display a timeline of when a project is scheduled to end. An end date is entered in A3, and the corresponding weekly cells are highlighted .

This works for most weeks, but some weeks are "shared" between months. Ex. 12/1/2017 is the first Friday of December, but using the WEEKNUM function and my current setup, this counts as the last week of November, so the cell for the first week of December (AX3) is not highlighted. For those types of dates, I need to highlight the following week also. 

Does anyone have suggestions on how to resolve this? This is part of a bigger worksheet that is otherwise working as expected, so I'd rather not make a huge change.

Test Spreadsheet

The following are the dates that fall into the previous week:

Feb 1-4 

March 1-4 

April 1 

May 1-6 

June 1-3 

July 1 

Aug 1-5 

Sept 1-2 

Nov 1-4 

Dec 1-2 

yogi_Make Up Teams of Specified Size By Randomly Selecting Players From A List

Google Spreadsheet   Post  #2140

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-26-2017

question by topdog2009:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/aBYNEq70AMk;context-place=forum/docs
Choosing Teams from a Range
With a group of people playing online games. :Looking for a way to randomly choose teams of two from a group of names.

Spreadsheet link https://docs.google.com/spreadsheets/d/10d6h7GS_kf-vjjxot3MZ5VU9t_KJp_MEG5jmuv9mQG0/edit?usp=sharing

Thanks

yogi_In Query Formulation By OP Sort By List (PRO CAP CLI DOCREP REC)

Google Spreadsheet   Post  #2139

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-26-2017

question by SATH59:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/1w_Ma70EteE;context-place=forum/docs
How to sort a query
Hi

I have this sample doc 

https://docs.google.com/spreadsheets/d/1MKQ1oBNSUdg8oVJsyD8lNLeTYL7d39BIjDXGkKvHTLI/edit?usp=sharing

Im using a Query in Tab Query

=query(arrayformula({
left(Pro!A2:A,3),Pro!A2:D,if(Pro!E2:E="","",to_date(value(Pro!E2:E))),Pro!F2:F;
left(Cap!A2:A,3),Cap!A2:D,if(Cap!E2:E="","",to_date(value(Cap!E2:E))),Cap!F2:F;
left(Cli!A2:A,3),Cli!A2:D,if(Cli!E2:E="","",to_date(value(Cli!E2:E))),Cli!F2:F;
left(Doc!A2:A,3),Doc!A2:D,if(Doc!E2:E="","",to_date(value(Doc!E2:E))),Doc!F2:F;
left(Rep!A2:A,3),Rep!A2:D,if(Rep!E2:E="","",to_date(value(Rep!E2:E))),Rep!F2:F;
left(Rec!A2:A,3),Rec!A2:D,if(Rec!E2:E="","",to_date(value(Rec!E2:E))),Rec!F2:F
}),"Select Col2,Col3,Col4,Col5,Col6,Col7 where Col5 <>'' and Col5 contains '"&B1&"' order by Col1, Col6 desc,Col3 ",0)

So this query is sorted by col 1 and it works, but i allways get the first column sorted by A to Z

Cap, Cli, Doc, Pro, Rec, Rep

What i need is to have it that whay but i want it to sort in different order, for example

First "Pro", then "Cap", then "Doc" then "Rep" then "Cli" ... i want to be able to sort them A to Z but in a different category order

I hope im explainig this well 

How can i achieve this ?

Thanks !!!

yogi_Combine An ARRAY Formula And A Vertical COUNTIFS Formula (per OP's spec)

Google Spreadsheet   Post  #2138

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-24-2017

question by Nathan_Lee:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/OIX0XP0lmms;context-place=forum/docs
How do I combine an ARRAY Formula and a vertical COUNTIFS formula?

I am trying to count the number of identical addresses/zip-codes that occur throughout their corresponding columns.  I can do this through a simple COUNTIFS formula, but as the spreadsheet grows, I need it to build out on its own.

=COUNTIFS(C:C,C2,D:D,D2)

I have attempted a corresponding Array Formula, but I don't know what I need to add/change in order to get it to count properly.  Any help would be appreciated!

=ARRAYFORMULA(IF(ROW(A:A)=1,"ARRAY - # of Events Here",IF(ISBLANK(B:B),"",COUNTIFS(C1:C,C1,D1:D,D1))))

The core of the formula is:

=ARRAYFORMULA(IF(ISBLANK(B:B),"",COUNTIFS(C1:C,C1,D1:D,D1)))

Below is a link to an example spreadsheet that shows what I am trying to accomplish:

https://docs.google.com/spreadsheets/d/1U8CsfFQ5yZwlieexaKime6dKMrkHutYP0j4HjEqvg54/edit?usp=sharing

Thanks for any help!

yogi_Compute Row By Row Time Worked by Team (T) Or Self (S)

Google Spreadsheet   Post  #2137

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-23-2017

question by LiquidLight:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/S0YY7Rs2nRU;context-place=mydiscussions

How to convert a column specific formula to an array formula?

I'm trying to convert a column specific formula to an array formula. I've having some difficulty and hoping someone out there can help :)

Row 1 houses my headings (and a few other array formula I've converted from column specific formula) but I'm stumped by this one.

This formula currently has to sit in each cell in column O (except O1 which is the heading):

=if(AND(count(J2:N2)=1,E2=0),"S",if(AND(count(J2:N2)>1,E2=0),"T",if(AND(count(J2:N2)>0,E2>0),"T",)))

E are hours worked by me on a specific job (new job on a new row)

J:N are the hours worked by other staff on the same job - it doesn't matter who they are, J:N just allows 5 cells for up to 5 other staff to be included in the same job (it may be these are all empty, it may be that only one or two have values) 

O is where I want this array formula to return whether the other staff have worked solo "S" on the job or teamed "T" with each other or with me.

"S" will show when COUNT(J2:N2)=1 and E2=0

"T" when either COUNT(J2:N2)>1 and E2=0     or     COUNT(J2:N2)>1 and E2>0

The formula above does this nicely... until I try to make it an array formula with the heading "Solo or Teamed?"

I'm getting stumped by the COUNT function I think. I started with this:

=ArrayFormula( if(row($A:$A)=1,"Solo or Teamed",if( len($A:$A),if( AND(count($J:$N)=1,E:E=0),"S",if( AND(count($J:$N)>1,E:E=0),"T",if( AND(count($J:$N)>0,E:E>0),"T",))),)))

...and progressively I got more and more lost!

Any help would be warmly received.

Cheers