How to extract months with data and find n-th value as starting point and n-th value as ending point-CodePudding

I have a data set which consists of Date/Time, Pressure and Custom Column. This represents pressure over time data, where I wanna know my starting point (after 5 minutes) and ending point of -before last value (row) within one month. To help you a bit out, usually the measurements are taking roughly 30-40 mins what you can see on this example down. So it means the amount of data can vary.

The Time column is calculated using:

=([@[Date/Time]]-I5)*1440 L5

This data set represents whole data and all the months with values, and I need separated (filtered) months with these starting/ending points as on the screenshot. I used Power Query a lot to play with data, but maybe there is another method to obtain those values...and make them dynamic when possible for future data.

I will also upload my dummy workbook with whole data set (all the months), filter table with months if needed for your infos and test.

In Office/Excel 365

Filter Column (eg for January 2020)

E4: 1/1/2020
E5: 1/1/2020

Results

F4 (date/time 5th minute): =IF(COUNTIFS(Table1[Date/Time],">="&E4,Table1[Date/Time],"<" & EDATE(E4,1))=0,"",
LET(x,FILTER(Table1[Date/Time],(Table1[Date/Time]>=E4)*(Table1[Date/Time]<EDATE(E4,1))),
          y, (x-INDEX(x,2))*1440,
          z, XMATCH(5,y,1),
          INDEX(x,z,1)))

G4: (Pressure 5th minute): =IF(F4="","",
LET(x,FILTER(Table1,(Table1[Date/Time]>=E4)*(Table1[Date/Time]<EDATE(E4,1))),
          y, (INDEX(x,0,1)-INDEX(x,2,1))*1440,
          z, XMATCH(5,y,1),
          INDEX(x,z,2)))

F5: (Date next to last): =IF(COUNTIFS(Table1[Date/Time],">="&E5,Table1[Date/Time],"<" & EDATE(E5,1))=0,"",
LET(x,FILTER(Table1[Date/Time],(Table1[Date/Time]>=E5)*(Table1[Date/Time]<EDATE(E5,1))),
          INDEX(x,COUNT(x)-1)))

G5: (Pressure next to last):=IF(F5="","",
LET(x,FILTER(Table1,(Table1[Date/Time]>=E5)*(Table1[Date/Time]<EDATE(E5,1))),
          INDEX(x,COUNT(INDEX(x,0,1))-1,2)))