The general formula as you asked is:
Using fixed values for today "02/10/2019" for dates the above formula would be:
We use eomonth(today(),-1), so we take as our starting date the last day of the previous month.
We multiply the whole formula by -1 because we ...
The key word in your formula is not Query but GOOGLEFINANCE.
Your query is correct. But please take into account the following.
As mentioned under GOOGLEFINANCE documentation page:
attribute is one of the following for historical data:
"open" - The opening price for the specified date(s).
"close" - The closing price for the specified date(s).
I am used to doing this in Open Office. Being UK I used the dd/mm/yyyy format, which Google does not recognise. So if I enter 2/10/2019, adding 1 produces 2/11/2019.
Choose Oct/2 as format and then you can just do =[original cell] + 1 to get Oct/3.
To separate date-time values formatted as 2018-11-01T20:16:52 (text values)
use an open-ended reference (B2:B)to split values added lately without having to edit the formula
use T as separator.
use ARRAY_CONSTRAIN to return split the values of non-empty cells.
Use COUNTA ...
I figured it out. Not the most elegant solution but it works. The "var col = s.getRange(4,1).getValue();" is just a MATCH function. I'd still prefer to have the row lookup without hardcoding the row number but this works until I add stuff. The bit at the end just resets the data entry spaces to match the "recommended" amounts by formula.
INDEX use 1 based indexes, meaning that the indexes start on 1. By the other hand to make QUERY return no column headers use use the clause label to set the column label to empty strings. Example:
=INDEX(QUERY($A$3:$K; "SELECT sum (F) where C='Reg'and K='"& K15&"' label sum (F) ''");0)
Solution with a Formula :
A1 : =ArrayFormula(if(
text((round(ROW(A1:A100)/4)*7)+43730,"dddd dd mmm yy")
Solution with drag and drop
Enter the dates in the cells : A1 is the first sunday,
A5 is the second with this formula =A1+7
A simpler solution (single formula) than the one on the other answer.
Add the following formula to C2
This is similar but not exactly the same content from my answer to the related questions. On the other answers, instead of 0 a blank ...
A simpler formula than the one on the other answer.
Add the following formula to C1
In case that you have a larger source data, Replace A1:B3 and A1:A3 accordingly.
Then do the Copy / Paste as values only steps, either by using the Google ...
It looks like you are looking for a single formula solution but they could be hard to understand and maintain, specially if you aren't familiar on how the Google Sheets related features works.
Dates. Google Sheets handles dates as serialized numbers, where a day is one unit.
Because of the above feature we could create a list of dates by ...