Select dates when first and last occurrence of a value below date row occurs

[Mickeyflip]

I need help with two things.

I have a set of daily work duties showing a day off work as "24" and a day of work as a blank cell. I need to identify the first and last date that each set of work days occurs, for each worker, for each set of work days in the month.

Second, a list of dates that are days off. The days off and work days are different for each person.

Below I've shown how the work days are listed, just days of the month with 24 as a work free day, and a blank cell as a day of work.

I need the output in the format as shown in rows 5 through 26 of the below spreadsheet

[Bo_Ry]

Please try at
B7
=CHOOSE({1,2},TRANSPOSE(FILTER($B$1:$AE$1,(INDEX($B$2:$AE$3,MATCH(B$5,$A$2:$A$3,),)="")*(INDEX($A$2:$AD$3,MATCH(B$5,$A$2:$A$3,),)<>""))),TRANSPOSE(FILTER($B$1:$AE$1,(INDEX($B$2:$AE$3,MATCH(B$5,$A$2:$A$3,),)="")*((INDEX($C$2:$AF$3,MATCH(B$5,$A$2:$A$3,),)<>"")+($C$1:$AF$1="")))))

B11
=TRANSPOSE(FILTER($B$1:$AE$1,INDEX($B$2:$AE$3,MATCH(B$5,$A$2:$A$3,),)))

[Mickeyflip]

Bo_Ry,

Greatly appreciated.

Works great in cells B7 and B11. Modified B7 slightly for C7: CHOOSE(1,2) to CHOOSE(2,2) in cell C7 to get proper end date for C7 and copied B7:C7 to D7:E7 and works correctly there (thanks for the reference to B5)

Also B11 works correctly, and does correctly in D11 when pasted there.

Where I am stuck is B8 and B12. Any idea how I can modify to get the next set of work days? B8 should return 14 JUN and B12 should return 2 JUN.

The trick with B8 - B10 is to ignore the previously selected work blocks and go to the next. Same concept with B11 and below, I need it to ignore previous 24's and just go to the next date where a "24" occurs.

Any suggestions?

[Bo_Ry]

Please check the file upload in Post #2

This is dynamic array Office 365, the formula in B7 spill to cover B7:C10
No need to copy formula to C7 or B8:C10

C10 font color in formula bar is gray out, because of no formula there.

Dynamic.jpg

[Mickeyflip]

Might help if i use my Excel 365 not the old one on my MAC. I'll report back!

[Bo_Ry]

Here' s the formula for older version

B7
=IFERROR(AGGREGATE(15,6,$B$1:$AE$1/(INDEX($B$2:$AE$3,MATCH(B$5,$A$2:$A$3,),)="")/(INDEX($A$2:$AD$3,MATCH(B$5,$A$2:$A$3,),)<>""),ROWS(B$7:B7)),"")

C7
=IFERROR(AGGREGATE(15,6,$B$1:$AE$1/(INDEX($B$2:$AE$3,MATCH(B$5,$A$2:$A$3,),)="")/((INDEX($C$2:$AF$3,MATCH(B$5,$A$2:$A$3,),)<>"")+($C$1:$AF$1="")),ROWS(B$7:B7)),"")

B11
=IFERROR(AGGREGATE(15,6,$B$1:$AE$1/(INDEX($B$2:$AE$3,MATCH(B$5,$A$2:$A$3,),)=24),ROWS(B$11:B11)),"")

[BMV]

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[Bo_Ry]

New formula at
B7:E10
=IFERROR(AGGREGATE(15,6,$B$1:$AE$1/($A$2:$A$3=LOOKUP("z",$B$5:B$5))/($B$2:$AE$3=0)/(($A$2:$AD$3>0)+($C$2:$AF$3>0)+($C$1:$AF$1=0)),ROWS(B$7:B7)*2-MOD(COLUMNS($B7:B7),2)),"")


or 365 Dynamic array
=AGGREGATE(15,6,$B$1:$AE$1/(INDEX($B$2:$AE$3,MATCH(B$5,$A$2:$A$3,),)=0)/((INDEX($A$2:$AD$3,MATCH(B$5,$A$2:$A$3,),)>0)+(INDEX($C$2:$AF$3,MATCH(B$5,$A$2:$A$3,),)>0)+($C$1:$AF$1=0)),SEQUENCE(4,2))

[Mickeyflip]

All that now works great so consider it solved! My initial problem was I'm not an "insider" so i was having some issues on my PC with 365. No spill allowed unless I do that. Became an insider on the MAC, updated the app and all good now with all forms of the equation. Last two work on the PC.

Thank you all.


I have one more question that I should know, but I'll search first then ask!