Forum Discussion
How to make Excel calculate if X is present within time period.
If you work with danish Excel the whole formula has to be translated into danish. If you open my attached file the formula is translated according to your regional setting. The formula only works in Excel for Microsoft 365, Excel for Microsoft 365 for Mac and Excel for the web.
Below is what i get from a translator but i assume that in danish Excel there isn't a BYROW formula and the TEKST format "DD.MM.YYYY" is certainly different in danish Excel. And i wonder if SUM, LAMBDA and LET are the same in english and danish Excel.
=LET(timeperiod;BYROW(SEKVENS(A2-A73;;0);LAMBDA(x;HVIS(SUM(FILTRER(B2:B73;(A2:A73<=DATO(ÅR(A2);MÅNED(A2);DAG(A2)-x))*(A2:A73>=DATO(ÅR(A2)-3;MÅNED(A2);DAG(A2)-x+1))))>3550;TEKST(DATO(ÅR(A2);MÅNED(A2);DAG(A2)-x);"DD.MM.YYYY")&"-"&TEKST(DATO(ÅR(A2)-3;MÅNED(A2);DAG(A2)-x+1);"DD.MM.YYYY");"")));
len;BYROW(timeperiod;LAMBDA(x;LÆNGDE(x)));
HVIS.FEJL(FILTRER(timeperiod;len);"no result"))
I was getting errors on "FILTER" though, so I changed it to the danish "FILTRER". Which means "to filter".
After that I'm getting #NAVN? (#NAME?) error. From a quick google search that indicates an error in the formula name, but isn't the formula name here "LET"? (Again excuse my lack of knowledge).
Here is the formula I have at this moment:
=LET(timeperiod;BYROW(SEQUENCE(A2-A73;;0);LAMBDA(x;IF(SUM(FILTRER(B2:B73;(A2:A73<=DATE(YEAR(A2);MONTH(A2);DAY(A2)-x))*(A2:A73>=DATE(YEAR(A2)-3;MONTH(A2);DAY(A2)-x+1))))>3550;TEXT(DATE(YEAR(A2);MONTH(A2);DAY(A2)-x);"DD.MM.YYYY")&"-"&TEXT(DATE(YEAR(A2)-3;MONTH(A2);DAY(A2)-x+1);"DD.MM.YYYY");"")));len;BYROW(timeperiod;LAMBDA(x;LEN(x)));IFERROR(FILTRER(timeperiod;len);"no result"))
Thank you for your time and effort.
If you work with danish Excel the whole formula has to be translated into danish. If you open my attached file the formula is translated according to your regional setting. The formula only works in Excel for Microsoft 365, Excel for Microsoft 365 for Mac and Excel for the web.
Below is what i get from a translator but i assume that in danish Excel there isn't a BYROW formula and the TEKST format "DD.MM.YYYY" is certainly different in danish Excel. And i wonder if SUM, LAMBDA and LET are the same in english and danish Excel.
=LET(timeperiod;BYROW(SEKVENS(A2-A73;;0);LAMBDA(x;HVIS(SUM(FILTRER(B2:B73;(A2:A73<=DATO(ÅR(A2);MÅNED(A2);DAG(A2)-x))*(A2:A73>=DATO(ÅR(A2)-3;MÅNED(A2);DAG(A2)-x+1))))>3550;TEKST(DATO(ÅR(A2);MÅNED(A2);DAG(A2)-x);"DD.MM.YYYY")&"-"&TEKST(DATO(ÅR(A2)-3;MÅNED(A2);DAG(A2)-x+1);"DD.MM.YYYY");"")));
len;BYROW(timeperiod;LAMBDA(x;LÆNGDE(x)));
HVIS.FEJL(FILTRER(timeperiod;len);"no result"))
- OliverScheurich thank you so much. 🙂
Changing the date format to DD.MM.ÅÅÅÅ fixed it, seems as though LAMBDA and LET are indeed the same in Danish, SUM is also i just wasn't uncertain about that one.
Again thank you for your time and effort, it is very much appreciated.You are welcome. Thank you for your very interesting question.