Forum Discussion

ClausDaniel's avatar
ClausDaniel
Copper Contributor
Oct 17, 2023
Solved

How to make Excel calculate if X is present within time period.

Hello,   First time posting, so let me just get straight to it.   I have a long period of time, where I have digital registration information about salary.   This includes Registration date for...
excel
Formulas and Functions
office 365
  • OliverScheurich's avatar
    OliverScheurich
    Oct 18, 2023

    ClausDaniel 

    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"))

ClausDaniel's avatar
ClausDaniel
Copper Contributor
Oct 18, 2023

OliverScheurich 

 

Thank you for your reply, maybe it is my lacking understanding of Excel in general, but I can't make your formula work for my dataset. 

 

Below is an example of a fictional dataset that would be like the ones I would have to do this task on.

So lets take the first row as an example, the registration date is 28-09-2023. Meaning that i need to know if there is 3848 in the column "hours", in the periode 29-09-2020 - 28-09-2023 (3 years). But that is just an example, like in your formula the period that has the 3848 hours can be in any 3 year period with in the allowed dataset. (It would also be preferable if the period that fulfills the 3848 hours, would be visible in some way. Maybe if Row A5 - A50 fulfills the 3848 hours, it would be shown that it is these specific rows that fulfill it).

 

So here is an example dataset:

Reg. date.Hours
28-09-202329,5
30-08-20231,5
28-07-202329,75
28-06-20230
28-06-202368,54
29-06-202336,5
27-05-20230
27-05-20235,61
27-05-202328,75
27-04-202328,5
26-04-202368,58
26-04-20230
30-03-20236
29-03-2023160,33
24-02-2023152,83
25-02-20231,5
28-01-20237
27-01-2023159,29
29-12-202220
28-12-2022160,33
28-11-2022160,33
29-11-202210
27-10-2022160,33
28-10-202213,75
28-09-2022152,83
29-08-202281,5
28-07-202210,75
28-07-2022160,33
26-07-202241,25
29-06-202218,5
27-06-202296
26-05-202211,5
30-05-202299,75
28-04-202213,75
28-04-2022120
30-03-202218,75
28-03-202288,75
25-02-202211,33
25-02-202255,25
10-01-20230
28-01-202261,25
29-12-20216,75
23-12-2021130
27-11-20216,25
29-11-202184,75
28-10-20216,75
28-10-2021132,25
29-09-202111,25
29-09-2021102,5
26-04-202132,03
26-03-202184,35
24-02-2021109,32
27-01-202193,38
27-01-20220
28-12-202081,09
22-12-202039
23-11-202076
26-10-202060
24-09-202060
24-08-202083,25
23-07-202062
28-03-202032,75
27-02-202079,5
19-01-20210
28-11-2019160,33
27-11-2019160,33
25-10-2019160,33
28-09-201950,27
25-08-201927
27-07-201930,56
27-06-201982,02
29-05-2019100
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 18, 2023

ClausDaniel 

=LET(timeperiod,BYROW(SEQUENCE(A2-A73,,0),LAMBDA(x,IF(SUM(FILTER(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(FILTER(timeperiod,len),"no result"))

 

This formula returns the periods for which the sum within 3 years is above 3550. In the sample data the formula would return "no result" for 3848 because there isn't a 3 year period with sum over 3848.

 

The assumption is that 28.09.2023 is the latest date and 29.05.2019 is the earliest date in the Reg. date. column. Then the formula calculates the sum for all ranges

 

28.09.202329.09.2020
27.09.202328.09.2020
26.09.202327.09.2020
25.09.202326.09.2020
24.09.202325.09.2020

 

and so on. Then it checks for each range if the sum is above 3550 and if so it returns the time range otherwise a blank cell. In the last step the data is filtered to only return the ranges with sum above 3550. For the sample data there are 24 time ranges of 3 years which sum up to over 3550. The sample data is in range A2:B73.

 

I'd sort the data in descending order of the Reg. date. column then it would be possible to return a result such as e.g. A5:A50 if the sum in this range is greater than 3550.

 

 

 

 

 

  • ClausDaniel's avatar
    ClausDaniel
    Copper Contributor
    Oct 18, 2023
    This seems like just the thing OliverScheurich
    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.
    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Oct 18, 2023

      ClausDaniel 

      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"))

      • ClausDaniel's avatar
        ClausDaniel
        Copper Contributor
        Oct 18, 2023
        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.

Resources