New answers tagged

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.


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