yogi_Compute Total Time Duration In hh:mm:ss For Set Of Times In Column A

Google Spreadsheet   Post  #2196

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

question by Jikuu:

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

Calculating difference and sum on a SPLIT array

I'm trying to calculate the total number of hours between time ranges in multiple cells at the same time

09:00-12:00

12:00-15:00

15:00-18:00

18:00-21:00

21:00-00:00

So first split on "-" to get the two times

=ArrayFormula(SPLIT(A1:A5,"-"))

Then calculate the difference between the two times and finally add them all up for the total number of hours. Not sure how to go about performing functions on all the results in an Array.

-Matt

yogi_Row By Row Sum Of Specified Number Of Non-Zero Entries

Google Spreadsheet   Post  #2195

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

question by advantagepro:

https://productforums.google.com/forum/#!topic/docs/UpsF7OTdCKs;context-place=forum/docs

SUM of values from first four columns that contain a value

https://docs.google.com/spreadsheets/d/1U5o_H3eCjjm3BLCyhj2Kv3keRimPwxTVeuTyC84YWWU/edit#gid=0

I want to create a formula that will add up the values from the first 4 columns in a row that contain a value. I created this sheet to demonstrate the values I would want it to return.

yogi_Count responses per hour in google forms spreadsheet

Google Spreadsheet   Post  #2194

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

question by zeke29:

https://productforums.google.com/forum/#!topic/docs/83vr5nLeSp8;context-place=forum/docs
Count responses per hour in google forms spreadsheet

I have a google forms where I capture the user's email and I need to count how many responses are submitted per hour by each email (user). 

Forms enters a time stamp in the form of 

MM/DD/YYY HH:MM:SS

I assume that I just need to capture the HH part of the time stamp and count the occurrences for each email but I'm not sure how to do that. Any help? 

Thanks

yogi_Count Instances Of Entities By Specified Age Groups

Google Spreadsheet   Post  #2193

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

question by sarahcatherine:

https://productforums.google.com/forum/#!topic/docs/s_05I07vtGA;context-place=forum/docsFilling blank date cells with previous entry

Adding up instances in a spreadsheet that meet a certain criteria

I have a complicated spreadsheet issue... I am collecting a lot of data in a form. It includes names of individuals along with sex and birth dates.  I need an equation that can simplify the data and identify the number of adult males, adult females, children, babies and newborns based on the dates of births and sexes entered. Sample spreadsheet.

I think I need to use the countifs function but can't seem to get it work... 

Any ideas? 

yogi_Fill In Blank Date Cell With Previous Entry

Google Spreadsheet   Post  #2192

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

question by REBECCA LEIGH CORREA:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/2YWx5Ean_7k;context-place=forum/docs
Filling blank date cells with previous entry

how to fill blank cells with date from the previous not blank cell.

yogi_Compute Days Elapsed Between Signing Of Contract And Implementation Date ...

Google Spreadsheet   Post  #2191

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

question by erijohns:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/7F67kIklQWw;context-place=mydiscussions
If date is before another...

Hi!

I've been reading through the Forum for the past couple days and learned a ton, but I haven't a seen a use case like this one and I'm stuck: 

My company has a product that can be implemented one of two ways. I have a spreadsheet with three columns: the Contract Signed Date, Go Live Date (Option A), and Go Live Date (Option B). 

Some clients have fully implemented our product, some have just implemented on one or the other, and some have only signed a contract -- they're being implemented but aren't live yet. 

I want to calculate how long, in days, it takes a given client to go live for the first time with our product, and if they aren't live yet, how long it's been since they signed the contract. I can figure out how to calculate how long it's been since Signed Contract, and I can figure out how to calculate DATEDIF between Signed Contract and one of the Go Live Dates, but if a client has fully implemented with us, then they have values in all three columns: they signed a contract, went live with Option A, and went live with Option B. 

So the trouble I have is: how do I create a formula that, if it sees two go-live dates (values in columns E, F and G), picks the one closest to the Signed Contract date and calculates how long it took to go live that first time? 

Here are the five scenarios I could think up: 

1. F=Blank, G=Blank (in which case, calculate E to Today)

2. F<>Blank, G=Blank (in which case, calculate E to F)

3. F=Blank, G<>Blank (in which case, calculate E to G)

4. F<>Blank, G<>Blank, F>G (in which case, calculate E to F, I think)

5. F<>Blank, G<>Blank, F<G (in which case, calculate E to G, I think)

It's possible that I'm not interpreting how a date could be less than or greater than another date. That's a bit confusing. 

Oh and here's some example data: 

1. E                   F                     G

  1/1/17  

The correct answer would be: 171 (when I posted this topic).

2. E                   F                     G

 1/1/17         1/2/17

The correct answer would be: 1

3. E                   F                     G

 1/1/17                                1/2/17

The correct answer would be: 1

4.  E                   F                     G

 1/1/17          1/5/17           1/4/17

The correct answer would be: 3

5.  E                   F                     G

 1/1/17         1/10/17          1/20/17

The correct answer would be: 9

Hopefully this makes sense. Here's my best attempt at a formula so far. It's so close, but I'm not expressing myself well enough. 

=IF(AND(F8<>G8<>“”,F8<G8),DATEDIF(E8,F8,“D”),IF(AND(F8<>G8<>“”,F8>G8),DATEDIF(E8,G8,“D”),IF(AND(ISBLANK(F8),ISBLANK(G8)),DATEDIF(E8,TODAY(),“D”),IF(AND(ISBLANK(F8),NOT(ISBLANK(G8))),DATEDIF(E8,G8,“D”),DATEDIF(E8,F8,“D”)))))

Thanks so much

yogi_Query To Pull Select Columns Of A Large File To Be Brought In By using ImportRange Function

Google Spreadsheet   Post  #2190

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

question by Sarvan Dharavath:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/5KFMydOQWhw;context-place=forum/docs

Filter only specific columns using importrange from one spreadsheet another spreadsheet

Hi, I am trying to import specific ranges from one spreadsheet to another using below formula (I found in Google forum), but it is not working(As it says "Result Too large") for me Please help. Below is my formula

=QUERY(ImportRange("1evyx3-EHSIP6VRtisBBocvDqgLW_90SQWnej-i1Ug2w","Part Requirement!A:BZ"),"select Col1, Col3, Col4, Col5, Col6, Col8 where Col57 ='Need to be Delivered'")

And below are my sheets

Source sheet:

https://docs.google.com/spreadsheets/d/1evyx3-EHSIP6VRtisBBocvDqgLW_90SQWnej-i1Ug2w/edit?usp=sharing

Target Sheet:

https://docs.google.com/spreadsheets/d/124p7VGvf8kmbJ1SikRtWNQ_tnFfy-Fn8cObOowZbzZ8/edit?usp=sharing

yogi_RoundDown Round And RoundUp Entries In A4:A

Google Spreadsheet   Post  #2189

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

question by Robert Reiling:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/FZkqAiimEF0;context-place=forum/docs
How can I round down a number calclated to the next lowest 1/8 of an inch
I have a larege formula that the end result needs to be displayed as a fraction rounded down to the next lower 1/8 inch increment. I can not seem to find a number option for fractions or a round down formula to the next lower 1/8 inch increment.

Any help would be greatly appreciated. I know at minimum the fraction option is in Excel but have had no luck in sheets yet.

Thank you

yogi_Query Data Grouping By Month Names

Google Spreadsheet   Post  #2188

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

question by Pashenkov:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/WpBZQU1j5ko;context-place=forum/docs
QUERY WITH DATE LOST FORMAT
Hello everyone,  I need help with a query which I want to show sum values and group by month,  here is what I got Until now

=QUERY(query(AdminOppImportageDB!$A$1:Q;"select J, sum(H),sum(I),sum(H)*I where (K <> 'Perdida' and Q=1) group by J,I ");"select Col1, sum(Col4) where Col2 is not null group by Col1 label Col1 'Valor de Oportunidades',sum(Col4) ' '")

but is seems to be incomplete due to there are months repeated at the result,

please help me out with this,

thanks

https://docs.google.com/spreadsheets/d/1jMHBl2xT4_h3YFt1eoeoCfL-gninebQv_3-ApArzUAc/edit?usp=sharing

yogi_Match Last And First name Combinations In Another Sheet And Pull Corresponding Points

Google Spreadsheet   Post  #2187

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

question by J720:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Ex97B5LWtNg;context-place=forum/docs
How do I match data to a first & last name

I need help matching a specific number to a specific set of names. As you will see in the below link, I have three names separated by Last Name & First Name in "Sheet1". In Column D of "Sheet1" employees can accrue points, for example D2 for Jane Doe should have 12 points, D3 for Bill Zoe should have 8 points and Johnny Apple in D4 should have 3 points.

The points will be collected from "Sheet2" under cells associated with the "Sheet1" names. Without writing a formula like: =SUM(SUMIF(A:A,{"Zoe, Bill"},K:K)) that is written specifically for an employee's name, is there a way to match the points in Sheet1 column D to the names in Sheet1 Column A&B from the listed names in Sheet 2 row 1?

In Sheet2 row 1, the names are populated using: =Sheet1!A2&", "&Sheet1!B2

By applying this idea to Column D, adding and removing names to the entire workbook and formulas is simplified for me for the future.

I'd appreciate any suggestion.

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

yogi_Pull Only Those Column Of Table Where Not All Rows of a Column Are Equal To Zero

Google Spreadsheet   Post  #2186

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

question by BgibbzGaminh:

https://productforums.google.com/forum/#!topic/docs/KStTTxnUdFc;context-place=forum/docs
Table Simplfier Function

Hi, so I do stats for a small time E-sports league, and I'm looking for away to condense player character selection tables. Essentially, I have a table of players and characters, recording the frequency that each player selects a character. However, many characters don't get selected. I want to create a formula to regenerate the table while only including columns that have a sum greater than 0 (Only include characters that were selected).

Linked below is sample doc showing what I would like to do.

https://docs.google.com/spreadsheets/d/1Vb-98vdbBTN9rEg4Mv-0VlxhGxUC7UMeAvrCABut-iY/edit?usp=sharing

yogi_Split Row on New Line For All Entries In Column A

Google Spreadsheet   Post  #2185

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

question by Harrison Alley:

http://yogi--anand-consulting.blogspot.com/2013/07/yogisplit-row-on-new-line-for-all.html
Split Rows Based On New Line

 

I'm wondering how to do something similar on this sheet: https://docs.google.com/spreadsheets/d/1KzbO47hC2ETA6WSoh4rhcOUvAdvkb0b3H6SCbcv597E/edit?usp=sharing

yogi_Key-in WorkDay Hours For Projects Given 'Start Date' 'End Date' And 'WorkLoad' Per Day

Google Spreadsheet   Post  #2184

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

question by Trevor Brightman:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/XYZcDFjNGb0;context-place=forum/docs
Plotting workload across dates

Hello,

I'm looking for a way to automatically plot workload across a series of dates (calendar) so I can forecast the workload of a given day to assure our staffing levels will meet demand.

Attached is an example with the general idea.

Table 1 simply lists the projects with start and end dates, as well as a =NETWORKDAYS calculation of the number of week days between the start and end.

I also added a "workload" column (total hours for the project) and using the weekdays I calculated how many hours that project would be needed to be worked on each week day.

Here comes the tricky part. I want Table 2 to automatically plot the daily workload onto the calendar dates. I've filled in the data manually (in green) but I'm wondering if it can be done with some sort of formula.

Ideally this would allow me to quickly and easily tally the workload needed for various projects as workload and due dates change in Table 1.

I hope this makes sense, and I hope someone can help me out,

Thanks Internet Friends!

https://docs.google.com/spreadsheets/d/16oSOWZKS7ZMby9rO9YPJwSyjNCT3WKCN-AeU3ZDCAbo/edit?usp=sharing

yogi_Count Number Of Holidays And WeekEnds That Coincide With Holidays

Google Spreadsheet   Post  #2183

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

question by Artem Rider:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/LF45VpqS-F8;context-place=forum/docs
How to count weekeds which cross the school holidays ?