I’ve got a column of dates where I’ve contacted people in the format:
| Column A | Column B |
|---|---|
| dd/mm/yyyy [type of contact] ; dd/mm/yyyy [type of contact] ; dd/mm/yyyy [type of contact] ; | number of entries in the last 30 days |
| dd/mm/yyyy [type of contact] ; dd/mm/yyyy [type of contact] ; | number of entries in the last 30 days |
I want to find out how many contacts I’ve made to someone in the last 30 days and arrayformula the whole column (B) to have it auto updated. Any ideas?
I’ve been fiddling around with split() and left() and if() but to no avail…
I did think I could split the columns and do multiple nested else if() functions to test if the left(10) of each column was within 30 days, but there MUST be an easier way….
>Solution :
You may try:
=map(B2:B,lambda(Λ,if(Λ="",,let(Σ,split(Λ," ;"),counta(ifna(filter(Σ,isbetween(Σ,today()-30,today()))))))))
