google sheets
I am looking for a formula that can look at a cell value that contains a date and return the first Monday to occur after that date.
Examples (MM/dd/yyyy format):
10/22/2013 (Tuesday) would return 10/28/2013 (Monday)
10/28/2013 (Monday) would return 10/28/2013 (Monday)
The following formula will help you filter the dates.
=FILTER(sourceArray, arrayCondition_1,..., arrayCondition_30)
=SUM(FILTER(B2:B, RIGHT(A2:A, 4)="2012", LEFT(A2:A,LEN(A2:A)-5)="february"))
The first arrayCondition_1 retrieves the year, having 4 digits, taken from the RIGHT. The second arrayCondition_2 retrieves the month (text), based on the total length of the string minus 5 (digits year plus white space). The sourceArray in filtered accordingly and the result is summed up.
I've created an example file for you: FILTER DATE AS TEXT
Somehow this seems appropriate.
=ARRAYFORMULA(
QUERY(
{
IFERROR(IF(A2:A = "devops", A2:A, TRIM(REGEXEXTRACT(A2:A, "devops "))),""),
B2:B
},
"SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 LABEL SUM(Col2) ''"
)
)
copy / paste
=ARRAYFORMULA(QUERY({IFERROR(IF(A2:A = "devops", A2:A, TRIM(REGEXEXTRACT(A2:A, "devops "))),""), B2:B}, "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 LABEL SUM(Col2) ''"))
Best Answer
Let
B2the cell containing the date.or