The first problem is identifying the datas that belong to a certain interval of dates...
Your dates are in column A and the data to process in column B
Use E1 to set the "Starting date" and F1 for the "Ending date"
In E2 and F2 use the following formulas
These are the "modified starting & endig dates"
Now create a "named range":
-call it SLOT
-in the "refers to box" insert the formula
Excel Formula:
=OFFSET(Sheet1!$A$1,MATCH(Sheet1!$E$2,Sheet1!$A$1:$A$10000)-1,1,1+MATCH(Sheet1!$F$2,Sheet1!$A$1:$A$10000)-MATCH(Sheet1!$E$2,Sheet1!$A$1:$A$10000),1)
This assumes that we are working on Sheet1 with a max of 10k dates; adapt the formula for a different sheet name and a possible longer list of dates, but
strictly respect the syntax I used
At this point the range SLOT will refer to column B of the range of dates visible in E2-F2, that reflect the dates set in E1-F1, if they have been set
The result is shown in the green area of the attached XL2BB minishet
At this point if you use (for example)
=MIN(SLOT)
you will calculate the MIN of the "selected" range; in other words you may use SLOT in your formula to calculate what you need referred to the dates set in E1-F1
BR1 Sales_Ledger RECON(9.2.3) ott-2022.xlsm |
---|
|
---|
| A | B | C | D | E | F | G | H | I |
---|
1 | Dates | Values | | | 05-feb | 10-giu | | | |
---|
2 | 01-gen | 1 | | | 05-feb | 10-giu | | Slot Values | |
---|
3 | 04-gen | 2 | | | | | | 4 | |
---|
4 | 20-gen | 3 | | | | | | 5 | |
---|
5 | 25-gen | 4 | | | | | | 6 | |
---|
6 | 08-feb | 5 | | | | | | 7 | |
---|
7 | 12-feb | 6 | | | | | | 8 | |
---|
8 | 04-mar | 7 | | | | | | 9 | |
---|
9 | 15-mar | 8 | | | | | | 10 | |
---|
10 | 01-apr | 9 | | | | | | 11 | |
---|
11 | 15-apr | 10 | | | | | | 12 | |
---|
12 | 28-apr | 11 | | | | | | 13 | |
---|
13 | 12-mag | 12 | | | | | | 14 | |
---|
14 | 19-mag | 13 | | | | | | 15 | |
---|
15 | 27-mag | 14 | | | | | | | |
---|
16 | 30-mag | 15 | | | | | | | |
---|
17 | 16-giu | 16 | | | | | | | |
---|
18 | 20-giu | 17 | | | | | | | |
---|
19 | 28-giu | 18 | | | | | | | |
---|
20 | 14-lug | 19 | | | | | | | |
---|
21 | 15-lug | 20 | | | | | | | |
---|
22 | 23-lug | 21 | | | | | | | |
---|
23 | 25-lug | 22 | | | | | | | |
---|
24 | 31-lug | 23 | | | | | | | |
---|
|
---|