Gantt chart - Pull start/end dates

lvircks 

This will be easier if you have some values in the colored cells.

So, first select the range that contains the colored cells, then use Ctrl+H to find and replace blank green cells with green cells containing a 1. Like this:

(p.s. I had no idea until today that this was even possible, so TIL I guess!)

After that, you can use XLOOKUP using the default first-to-last search to return the start date:

=XLOOKUP(1,$B2:$O2,$B$1:$O$1,"")

This will look in cells B2 to O2 for a 1 and return the value from row 1 in the same column (i.e. the week commencing date).

And using a last-to-first search to return the end week, then add 4 to get the Friday of that week:

=XLOOKUP(1,$B2:$O2,$B$1:$O$1,"",0,-1)+4

This will look backwards from cell O2 to B2 for the first 1 it finds and likewise return the week commencing date from row 1, then add 4 to that.

Please see attached example.

If you want to create a single column with the date range, you can use this:

=TEXT(XLOOKUP(1,$B2:$O2,$B$1:$O$1,""),"m/dd")&"-"&TEXT(XLOOKUP(1,$B2:$O2,$B$1:$O$1,"",0,-1)+4,"m/dd")