I am trying to find a way to import Gantt charts from excel into Monday.com so that I can have the tasks and date ranges. I am trying to find a way (formula/programming) so that for each row I can pull the start date (first green cell for the row) and end date (first cell after green minus 3 days or last green cell plus 4 days to capture Friday being the last day for the project) from the headers. I have no idea if this is possible or how I would go about doing this. I am including a picture of what I am talking about. I have manually entered the date ranges in column V (preferred) or the start and end dates in separate columns X&Y. This is what I am trying to achieve.
Can anyone help?
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")Accepted Solutions
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")