yogi_From Timestamp Column Compute Number Of Instances An Entry was Submitted Day By Day (Google New Sheets)

yogi_Compute Sum of In And Out By Date From Table Of Transactions In Columns H Through K

                                           Google Spreadsheet   Post  #1964
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 25, 2015

question  by Josh Kaye:

https://productforums.google.com/forum/#!mydiscussions/docs/4W9DVjuDBV8

For looping natively in Sheets or a Compare/Match function

I'm trying to create a google doc to track finances over time. 

Basically I want to sum all entries from a certain date into a separate single entry of a corresponding date. The issue is that I don't know ahead of time how many entries there will be for a certain date. 

Here's what I have

So basically for 6/9/2015 in A, I want to sum all the values in G where the corresponding H value is 6/9/2015 and then put that sum into the D cell in the same row as the original A. 

Basically this behemoth for C7:

=(IF(H7=A7,F7,0)+IF(H8=A7,F8,0)+IF(H9=A7,F9,0)+IF(H10=A7,F10,0)+IF(H10=A7,F10,0)+IF(H11=A7,F11,0)+IF(H12=A7,F12,0))

Except that I have several categories beyond food and many rows to contend with. I had hoped that there was something resembling a for loop like one might use in java to go through the relevant columns and compare but I don't know of a way to do that natively in docs.

Is there a way to implement this without getting into writing my own script?

Thanks very much in advance. 

--------------------------------------------------------------------------------------------------------------------------------------

yogi_Count Number Of Consecutive Days of Run Back From Specified Date

                                           Google Spreadsheet   Post  #1963
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 25, 2015

question  by Miller Scott:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/-V9hBYm1cTs

Calculating Days Since Last X

Hi all. I run and log my miles in a google sheet. I want to create a function that calculates the number of days I have consecutively run and start over when I take a day off. 

I'm guessing I might have to combine a few functions to do this and I don't know what I don't know when it comes to making this happen. Is there any assistance this group can offer?  

I have linked an example and have added a few additional notes with in it. Comment there or here if you have thoughts on a solution. 

Thank you!

Running Log Example

------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Compute Cumulative Time From LOGBOOKS!G For A/C Model In LOGBOOK!C For Time Periods In Column A

                                           Google Spreadsheet   Post  #1962
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 21, 2015

question  by Matt Volf:

https://productforums.google.com/forum/#!mydiscussions/docs/Gdkry18ZwbA

Need help with a formula for my pilots logbook in sheets.

I need help figuring out a formula that will sum all flight time in a specified model of aircraft during a specified time period.

Example:

I want to sum all flight time in a Cessna 172 in the past 6 months.

---

Here is a link to the spreadsheet.

https://docs.google.com/spreadsheets/d/1sbANEyT6mX6wJvejWoTvkWSpKjVfKz_nssKRv-YqCeA/edit?usp=sharing

I am looking for a formula that will total all flight time (Logbook!G)  that was flown in a specific model (logbook!C) within in a specified time (past 30 days, past 6 months, whatever I specify)

--------------------------------------------------------------------------------------------------------------------------------

yogi_If Name Matches In Column D Of MASTER And Date In Column DH Of Master In The Corresponding Row Is Between Dates In Cells C1 And D1 Then return 0.5 Else 0

                                           Google Spreadsheet   Post  #1961
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 21, 2015

question  by J. Eric Smith:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/xldqi1BgpZo

Trying to do a nested If with dates, failing miserably. Please help!

So I have a fairly complex sheet that I'm trying to do too much with, but here I am. I'm trying to write a nested IF statement that will look for a name, if the name matches it then tries to match a date range. If all are true it should return "0.5", else "0"

=If(Master!D:D=A6,if(Master!DH:DH>=$C$1,if(Master!DH:DH<=$D$1,"0.5")),"0")

You can look at what I'm trying to do and what have tried at: https://docs.google.com/spreadsheets/d/1zFLHr2H0gWjwSW6xKz8e3w4XtArDa-erBhJf0u64Onc/edit?usp=sharing

It's the second sheet "Credits Earned by Date Range"

Thanks in advance for any suggestions or help.

-eric

-------------------------------------------------------------------------------------------------------

yogi_Count Row By Number Of Instances Of Specified Phrase In Rows Of Column A

                                           Google Spreadsheet   Post  #1960
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 21, 2015

question  by Anton Finneran:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/WrOQ3KvROMY

How to count multiple iterations of a text string in a cell

Hi all

How to I count the number of times a certain text string is found within a cell

For example - the string "ABC"

If cell A1  = "ABC XYZ ABC", I would want the result to be returned to be 2 or

if cell A2 = "XYZ AA ABC ABC ABC", the result would be 3

Ultimately, I want to count the iterations of the text string "ABC" in a range of cells - say column A

Any advice would be appreciated

Thanks

Anton

------------------------------------------------------------------------------------------------------------------

yogi_Convert Entries While Importing CSV Into Their Numerics While Preserving The Header Row As Textual

                                           Google Spreadsheet   Post  #1959
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 15, 2015

question  by Bishkopt:

https://productforums.google.com/forum/#!mydiscussions/docs/7LA0bBUpq1Q

Problem with importing CSV with semicolons (and commas as decimal marks)

Hi!

I'm looking for an elegant way to import and properly format CSV files delimited with semicolos and with commas as decimal marks.

I'm trying to do it in ONE sheet, with as little computation as possible needed.

I've figured out the following formula:

=arrayformula(substitute(importdata("http://www.noblefunds.pl/noblefunds/noblefunds_plugin/noblefunds/gencsv",";"), ",", "."))

(Note that there is an additional argument in IMPORTDATA fuction - it's not specified in the documentation, but it apparently works!).

It seems to do the work jus fine, but:

1) I cannot use functions such as "sum" (however I can add particular values)

2) I cannot get the date to be displayed in normal date format

Please see example spreadsheet: https://docs.google.com/spreadsheets/d/1dHK-wRVmOhs52BHsTeEmpw6kgwhxBTjIsICfN8_qyF8/edit?usp=sharing

Can you help, please?

-------------------------------------------------------------------------------------------

yogi_ From A Two-Line Input Per Cell In A Column Split First_Line And Second_Line Entries Row By Row In Adjacent Columns

                                           Google Spreadsheet   Post  #1958
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 15, 2015

question  by Zackary Simkover in

http://yogi--anand-consulting.blogspot.com/2013/07/yogisplit-row-on-new-line-for-all.html

Hi Yogi,
I'm trying to split based on carriage return (new line), but I want the first line of a cell's data to remain in column A (Name), and everything on the second line in column A moved over to column B (Personal Headline). How could I update your formula from this post to do this? Thanks!

Example of what I'm seeing - 
https://docs.google.com/a/checkmate.io/spreadsheets/d/1u6qQDS2w9pdu7LqPnnfsfYibKo-Ydgq2E3HfHfVpPrY/edit?usp=sharing
-------------------------------------------------------------------------------------------------------------------

yogi_From Table Of WeekDays And Names Of People Who Made Bookings Count Nuimber Of Bookings Made By Day Of Week

                                           Google Spreadsheet   Post  #1957
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 15, 2015

post by Johanna Hart:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/dIFaxU37jHs
Count only if 2 criteria are satisfied

I have a long list of days of the week (A), and a long list of peoples names (B) who made the bookings. I want to produce a table (let's say columns C & D) which will count the number of bookings made on each day of the week. The issue I seem to be encountering is that each of the cells in column B have formula in them, so if I use ISTEXT(B2) it counts the formula as text, even if the box is 'empty.' 

I have been creating a long column out of sight (let's say column E) to match the amount of data in columns A & B, and using a IF 'all of criteria in column B2 cell' (to avoid the ISTEXT issue) then, A2.  And then using my C & D table space to Count each of the days of the week in column E- =COUNTIF(E2:E500,C2).

I am sure that there must be an easier way to create a table (C & D) that counts the number of names in column B for each day of the week..?

Would appreciate your help very much!

A                              B                C                         D                   E

Monday                   Sam            Monday               3                   Monday

Monday                   Ed               Tuesday              1                   Monday

Monday                   Mary           Wednesday         2                   Monday

Monday                                      Thursday             1

Monday                                       Friday                 0

Tuesday                  Tom             Saturday            0                    Tuesday 

Wednesday             Sam             Sunday              0                    Wednesday

Wednesday             Sam                                                             Wednesday 

Wednesday

Thursday

Thursday                 Mary                                                            Thursday

Thursday

-------------------------------------------------------------------------------------------------

yogi_Rank Times That Include Fractions Of Seconds

                                           Google Spreadsheet   Post  #1956
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 14, 2015

post by Thomas Reeve:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/OyZoIP6LYiw
Exercise times in sheets

I'm helping a teacher with a spreadsheet that records rock climbing times. He wants to enter something like 3 minutes, 20.4 seconds into a cell and have it display as mm:ss.0. However,  he also wants to rank the times (1st place with fastest time, etc), which means I need this stored as a TIME value. [I have set up a RANK function for him in another cell, but it does not work if the times are text values.]

I have taught him to enter his data as "00:03:20.4" which enters the data as a time value. However, when a time is entered into the cell, the display format always reverts back to "hh:mm:ss" (hiding the tenths of a second). He can "correct" the format by changing the column to the mm:ss.0 format, but this extra step seems to be confusing. 

Is there a way to prevent the cell from reverting the format when I enter a time value?

--------------------------------------------------------------------------------------------------

yogi_Set Up QURY Formulation For Specified Criteria Involving Some Or All

                                           Google Spreadsheet   Post  #1955
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 13, 2015

post by Shashi Krishna:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/uXx8loY3tcQ

And/Or in an =QUERY condition question

Hey guys,

So I have a Sheet with 2 sheets in it. Sheet 1 has the following type of data:

------------------------------------------------------------------------------------------------------------------------------

NAME    ABSENCE TYPE        DATE FROM                 DATE TO

------------------------------------------------------------------------------------------------------------------------------

abc            Sick Leave                 01-05-2015                 03-05-2015

def             Personal Leave          02-04-2015                 04-04-2015

abc            Personal Leave          06-04-2015                 07-04-2015

...

etc.

On Sheet 2 I have the following formula going.....

=query({importRange("documentID","Sheet1!A2:N")},"select Col1,Col2,Col3,Col5,Col6,Col7,Col8,Col13 where Col2 <> '' and (Col3="&  "'" & H6 & "' or Col2="&  "'" & I6 & "')and Col11='Approved' and toDate(Col5)>=date '"& TEXT( E6 , "yyyy-MM-dd" ) & "' and toDate(Col5) <= date '"& TEXT( G6 , "yyyy-MM-dd" ) & "' " , 0 )

H6 in Sheet2 is the Absence Type and I6 in Sheet2 is Name of the person. 

How do I make the condition such that...

1. If name is provided then ALL absences of that person are shown.

2, If absence type AND name is provided then THAT SPECIFIC ABSENCE TYPE for THAT SPECIFIC USER is shown.

Since I am using an OR inside an AND...it works partially. Can someone please help?

Thanks.

Shashi

--------------------------------------------------------------------------------------