Extract fields based on current Date and Month

Dear Experts,

I do have a situation where I do need your support. Please find here attached a sample workbook with the expected output. The first expectation is completely based on the current running date. So if the current date is not matched with the approaching due date then it needs to there in the expected output. So suppose for any activity if the due date is Monthly Day 2 then Day 2 due to activity will fall into the next month bucket.

The second expectation is for Daily, As Needed, and As Received these items will be based on Month and not as per due dates.

Thir Expectation is for Every Tuesday due activity. So suppose if today is Tuesday then the activity should get refect based on Month and Day.

The reason for providing two expected out is supposed today is August 17, 2021, and we are still in August month accordingly I have provided for August 2021 expected output so when will touch to September 01, then for September month expected output also shared.

I hope I have clarified everything. Please feel free to revert in case of any further clarification if required. Dear Experts, I really do feel that situation is very complex and would be highly grateful if can be resolved with expected output.

Thank you for your valuable support and precious phase.

Regards,

Neilesh

I1 => 1 Aug 21

B2:H7
=IFERROR(INDEX(Details!B$1:B$28,AGGREGATE(15,6,ROW(Details!B$3:B$28)/IF((Details!$G$3:$G$28=$I$1)*(Details!$H$3:$H$28>"a"),1,(--SUBSTITUTE(Details!$H$3:$H$28,".","/")>TODAY())*(--SUBSTITUTE(Details!$H$3:$H$28,".","/")<=EOMONTH(TODAY(),0))),ROWS(B$3:B3))),"")

I9 => 1 Sep 21

B10:H18
=IFERROR(INDEX(Details!B$1:B$28,AGGREGATE(15,6,ROW(Details!B$3:B$28)/IF((Details!$G$3:$G$28=$I$9)*(Details!$H$3:$H$28>"a"),1,(--SUBSTITUTE(Details!$H$3:$H$28,".","/")>$I$9)*(--SUBSTITUTE(Details!$H$3:$H$28,".","/")<=EOMONTH($I$9,0))),ROWS(B$10:B10))),"")

Originally Posted by Neilesh Kumar

Hi Bo_Ry,

From B3:H7 first line item is missing as output for Dt. August 20, 2021. Since August 20, 2021, is approaching date so that activity needs to be covered in the list.

Please do the appropriate.

Regards,

Neilesh

This is what I see in my file on the post#3

Result.png

change date on Details column H to real date 20 Aug 2021 not text "20.8.2021"

the real date is number.
20 Aug 2021 when change format to general it will show 44428.

and also check if =today() return 17 Aug 2021

Originally Posted by Bo_Ry

change date on Details column H to real date 20 Aug 2021 not text "20.8.2021"

the real date is number.
20 Aug 2021 when change format to general it will show 44428.

and also check if =today() return 17 Aug 2021

Hi Bo_Ry, Can we use for all August month output in Column G based on Cell I2 (08/01/2021). Because of what's happening for half month i need to use criteria as I2 for September due dates i need to use criteria as 09/01/2021.

So what if we use the Current month formula for Cell I2 so as and when the month will get change accordingly the output will also get changed.

Thank you for your valuable support.

Regards,

Neilesh

Last try

B2
=IFERROR(INDEX(Details!B$1:B$26,AGGREGATE(15,6,ROW(Details!B$3:B$26)/((Details!$G$3:$G$26=$I$1)+(Details!$H$3:$H$26>TODAY())*(Details!$H$3:$H$26<EDATE($K$1,1))+(Details!$G$3:$G$26=$K$1)*(Details!$H$3:$H$26>"")>0),ROWS(B$3:B3))),"")