yogi_List In Cells A9:A Pay Period Dates Starting From Date In Cell B6 To End Of Month Of Date In B6

Google Spreadsheet   Post  #2488

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-16-2018

question by: Mobility Project PT
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/MndAN1XVBlQ;context-place=mydiscussions
Autofill dates for bi-monthly timesheet?
I have a timesheet to record worked hours. We have two pay periods that run from the 1st to 15th day of each month, and then from the 16th to the last day of the month. I have found a formula to autofill dates but can't figure out how to make it stop on the 15th (if starting on the 1st) or stop on the last day of the month (if starting on the 16th)

=ArrayFormula(ADD(A1,row(INDIRECT("A1:A"&16))))

What would you do? Thanks!

yogi_Compute Running Balance In Column C Given Deposits And Withdrawals In Columns A And B

Google Spreadsheet   Post  #2487

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-15-2018

question by: Elliana Lindsey
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/LdwPv_IZQMw;context-place=mydiscussions
=C1-B2+A2
C1-B2+A2 the result will appear on C2, how can i make it until 1000 column wihtout copy the formula until 1000 column?

yogi_Pull Into Cell C2 Unique Part Of Strings From Cells A2 And B2

Google Spreadsheet   Post  #2486

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-15-2018

question by: Krishna Goje
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/WjpB238Fnxc;context-place=forum/docs
Multiple strings comparison
Hello All,

I need a small help from you, what i want is compare the 2 different cells which has multiple strings and return the unique/mismatched values from both the cells.

Say:
CellA : Dilsukhnagar, Hyderabad, India
CellB : Gayathri Nagar, Hyderabad, India

Output : - Dilsukhnagar
         + Gayathri Nagar

Output must be in a single cell.

Any help will be appreciated.

Thanks,

yogi_Calculating Concurrency with Start Times/End Times and Duration

Google Spreadsheet   Post  #2485

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-11-2018

question by: Anders Vonderheyde
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/fHalIMqftkU;context-place=mydiscussions
Calculating Concurrency with Start Times/End Times and Duration
Hi all,

I'm looking to calculate chat concurrency. In my case, this is defined as: on average, how many chats is a rep handling at the same time? I have chat start times, end times, and chat durations listed in seconds. What is the best way of calculating this overall? I've included a screenshot of a little glimpse of the data below. Happy to share the doc with the full dataset with someone as well. Thanks!!!

yogi_Key-in Student First Name In Cell E2 And Pull All Student Information From 'Data' Sheet

Google Spreadsheet   Post  #2484

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-09-2018

question by: Junnie Chen
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/law8Pw5wjLw;context-place=forum/docs
VLookup Using Partial Values
I have a list of student names and their information in 'Data' sheet. Separately in 'Inquiry' sheet, I want to pull all information for that particular student once user key in the student name partially in cell E3. Currently I am using the formula below to extract the information but it does not work if I key in the name partially.

=IF($E$3="","",ARRAYFORMULA(TRANSPOSE(VLOOKUP($E$3,Data!$B$2:$Z$1000,{2},FALSE))))

I've included the sample sheet here. User would need to key in only partially of the name eg. "Alex" instead of full name "Alex Tan Man Man " and i need the rest of the info to be populated automatically. Kindly advise me how to fix this, many thanks!

yogi_Compute Sum Of The Maximum of Group of Specified Number Of Rows Of Numbers In A Column

Google Spreadsheet   Post  #2483

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-08-2018

question by: David J Spigelman
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/UWgK-rRzjFc;context-place=forum/docs
Sum of Max of every N-rows?
I want to be able to take a column full of numbers, and find the max values of every 3 rows. I then want to be able to total just those max values. So, for the list of values:

1
13
28
290
65
72
8
9
10

I want to be able to select 28, 290, and 10, which would then give me a total of 328. I've been able to find how to add every 3rd value, but not how to max them by 3 rows. I've looked at MAXIFS, as well as several SUMIF with ARRAYFORMULA, MOD and ROW functions thrown in. I'm guessing it actually is a solution involving those, but I can't seem to put it together right. 

Can anyone help with this, and explain how your solution works?

yogi_Conditionally Format B1:D1 When Date In Column A Is TODAY And Columns B:D Houses Off In Any Row

Google Spreadsheet   Post  #2482

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-05-2018

question by: Cary K
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/guf_6HBhZT4;context-place=forum/docs
Conditional formatting - Changing header cell color if two cells in row match criteria

Hi All!

I'm hoping someone can help me with a problem I'm having because it's definitely stumped me.  I consider myself to be pretty proficient with conditional formatting but am struggling trying to figure out how to change a header color based on the values of two cells in a row.  I have Names as headers (Row 1) and Dates in column 1.  The dates would actually be a list of all the dates in a calendar year.  In this example, I've just included a few from August.

What I'd like to do is when the date in column A equals today's date AND the value in the cell in column B for today's date is equal to "OFF", I'd like to have the Name1 header change to a specified color.  Same for Name2, Name3, etc if the value in their respective columns is "Off" and the date is today.  So as an example if today's date was 08/05/18 and the value in column B for that date was "Off" then the Name 1 header would change color.  Kind of like a intersection of the date and name if it's OFF.

I tried using =AND($A2=TODAY(),$B2="Off") in the "Custom Formula" portion of conditional formatting with the range being B1 but that didn't work.  Same with the following ...

=AND(A:A=TODAY(),B:B="Off")

=AND($A1:A=TODAY(),B1:B="Off")  as well as other formulas but none seem to work.

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

Any thoughts?  Or is this even possible using "Custom Formula" in conditional formatting?

Thanks!

Cary K