Forum Discussion
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 the payslip and number of working hours on the payslip.
So basically every month of salary has a line in Excel, and each line has registration date (column D) and number of hours (Column E). (and more).
Let's say I have 8 years of this data, and I need to know if there, anywhere in this 8 year period, is a 3 year period with 3848 or more hours. (The 3 year periode needs to be decided bu the registration date).
How would one do that? (keep in mind I am a novice in Excel).
Hopes my question makes sense.
Version is Microsoft office 365.
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"))
7 Replies
=SUM(N(BYROW(B1:B19,LAMBDA(x,SUM(x:OFFSET(x,4,0))>60))))
I can't rebuild your dataset therefore i've set up a small example to check if the sum within 5 days exceeds 60. In this example only the sum from 08.01.2023 to 12.01.2023 exceeds 60.
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-2023 29,5 30-08-2023 1,5 28-07-2023 29,75 28-06-2023 0 28-06-2023 68,54 29-06-2023 36,5 27-05-2023 0 27-05-2023 5,61 27-05-2023 28,75 27-04-2023 28,5 26-04-2023 68,58 26-04-2023 0 30-03-2023 6 29-03-2023 160,33 24-02-2023 152,83 25-02-2023 1,5 28-01-2023 7 27-01-2023 159,29 29-12-2022 20 28-12-2022 160,33 28-11-2022 160,33 29-11-2022 10 27-10-2022 160,33 28-10-2022 13,75 28-09-2022 152,83 29-08-2022 81,5 28-07-2022 10,75 28-07-2022 160,33 26-07-2022 41,25 29-06-2022 18,5 27-06-2022 96 26-05-2022 11,5 30-05-2022 99,75 28-04-2022 13,75 28-04-2022 120 30-03-2022 18,75 28-03-2022 88,75 25-02-2022 11,33 25-02-2022 55,25 10-01-2023 0 28-01-2022 61,25 29-12-2021 6,75 23-12-2021 130 27-11-2021 6,25 29-11-2021 84,75 28-10-2021 6,75 28-10-2021 132,25 29-09-2021 11,25 29-09-2021 102,5 26-04-2021 32,03 26-03-2021 84,35 24-02-2021 109,32 27-01-2021 93,38 27-01-2022 0 28-12-2020 81,09 22-12-2020 39 23-11-2020 76 26-10-2020 60 24-09-2020 60 24-08-2020 83,25 23-07-2020 62 28-03-2020 32,75 27-02-2020 79,5 19-01-2021 0 28-11-2019 160,33 27-11-2019 160,33 25-10-2019 160,33 28-09-2019 50,27 25-08-2019 27 27-07-2019 30,56 27-06-2019 82,02 29-05-2019 100 =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.2023 29.09.2020 27.09.2023 28.09.2020 26.09.2023 27.09.2020 25.09.2023 26.09.2020 24.09.2023 25.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.
