New answers tagged google-sheets-dates
0
I'll offer a suggestion, but understand that I'm making it based on limited information as I still await answers to my comment-questions on the original post.
If you wanted every non-Friday date between today and June 1, 2021:
=FILTER(SEQUENCE((DATEVALUE("June 1, 2021")-TODAY())+1,1,TODAY()),WEEKDAY(SEQUENCE((DATEVALUE("June 1, 2021")-...
0
EDITED (following OP's sharing a test sheet)
Delete everything bellow B6 and use
=INDEX(IF(WEEKDAY(B6:B)=6,,B6:B))
Initial answer
You can use the following
=ArrayFormula(WORKDAY.INTL(C3,sequence(13,1),"0000100"))
C3 can be any date of your choice.
You can also expand the sequence to any number of rows/dates by changing 13 to any number you like.
...
1
I have duplicated your "Transaction" sheet and renamed it "Erik Help." In that sheet, I've deleted your manual data in Column E and placed the following formula in cell E1:
=ArrayFormula({"Rate";IF(A2:A="",,IFERROR(VLOOKUP(C2:C&A2:A,QUERY({Rates!A2:A&Rates!C2:C,Rates!B2:B},"Select * Where Col2 Is Not Null ...
2
EDIT
(based on OP's comment)
... I need to count them only if they are part of the same week.
Use the following formula
=QUERY(ARRAYFORMULA({A1:A, B1:B, WEEKNUM(B1:B,2)}),
"select Col1, count(Col3)
where Col1 is not null
and Col3="&WEEKNUM(TODAY(),2)&"
group by Col1, Col2
...
1
Please use the following formula
=SEQUENCE(COUNT(B5:B),1,DATE(A2,MONTH(A1&1),1),1)
You then format your cells as Tue found under Format >Number >More date and time formats
I also noticed that you have the following conditional formatting applied, which will no longer work
=OR($A5="Fri",$A5="Sat")
What you should now use is
=...
1
What you actually want is to sort your data based on column A.
To do this you do not need a helper column (like column E).
This is the simpler formula you need to use
=SORT(A2:D,ArrayFormula(IFERROR(TEXT(VALUE(REGEXEXTRACT(A2:A,"/(\d+)")),"00"))),1)
1
Try this in place of your formula:
=ArrayFormula(TEXT(VALUE(REGEXEXTRACT(FILTER(A2:A,A2:A<>""),"/(\d+)")),"00"))
By converting the values of the REGEXTRACT portions to text in the format "00", you'll have leading zeros in front of otherwise single digits and should be able to sort normally.
0
How to fill dates by exactly one year by dragging the cell.
Please do the following:
First create the second cell and then select and drag BOTH.
It has been asked before but never answered correctly.
-1
=Sum(B9:B11)/D1
D1= Countif(A9:A11,">0")
2
You mentioned:
Can A2 be some pointer to a cell that has the text "A2" and it always stays as A2 in the formula -- same question for E2 ?
You do not need to reference a pointer to an extra cell.
What you are looking for is the INDIRECT function.
You can replace your ranges
Sheet1!A2:A and Sheet1!E2:E with
INDIRECT("Sheet1!A2"&":...
Top 50 recent answers are included
