SOLVED

Gantt chart - Pull start/end dates

%3CLINGO-SUB%20id%3D%22lingo-sub-1520194%22%20slang%3D%22en-US%22%3EGantt%20chart%20-%20Pull%20start%2Fend%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1520194%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20find%20a%20way%20to%20import%20Gantt%20charts%20from%20excel%20into%20Monday.com%20so%20that%20I%20can%20have%20the%20tasks%20and%20date%20ranges.%20I%20am%20trying%20to%20find%20a%20way%20(formula%2Fprogramming)%20so%20that%20for%20each%20row%20I%20can%20pull%20the%20start%20date%20(first%20green%20cell%20for%20the%20row)%20and%20end%20date%20(first%20cell%20after%20green%20minus%203%20days%20or%20last%20green%20cell%20plus%204%20days%20to%20capture%20Friday%20being%20the%20last%20day%20for%20the%20project)%20from%20the%20headers.%20I%20have%20no%20idea%20if%20this%20is%20possible%20or%20how%20I%20would%20go%20about%20doing%20this.%20I%20am%20including%20a%20picture%20of%20what%20I%20am%20talking%20about.%20I%20have%20manually%20entered%20the%20date%20ranges%20in%20column%20V%20(preferred)%20or%20the%20start%20and%20end%20dates%20in%20separate%20columns%20X%26amp%3BY.%20This%20is%20what%20I%20am%20trying%20to%20achieve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22lvircks_0-1594697026393.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F205124i94BDFA64BF2AC6D6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22lvircks_0-1594697026393.png%22%20alt%3D%22lvircks_0-1594697026393.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1520194%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1521363%22%20slang%3D%22en-US%22%3ERe%3A%20Gantt%20chart%20-%20Pull%20start%2Fend%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1521363%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F727647%22%20target%3D%22_blank%22%3E%40lvircks%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20be%20easier%20if%20you%20have%20some%20values%20in%20the%20colored%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20first%20select%20the%20range%20that%20contains%20the%20colored%20cells%2C%20then%20use%20Ctrl%2BH%20to%20find%20and%20replace%20blank%20green%20cells%20with%20green%20cells%20containing%20a%201.%20Like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22OwenPrice_0-1594743745474.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F205201iF4AEDB31694A8E04%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22OwenPrice_0-1594743745474.png%22%20alt%3D%22OwenPrice_0-1594743745474.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E(p.s.%20I%20had%20no%20idea%20until%20today%20that%20this%20was%20even%20possible%2C%20so%20TIL%20I%20guess!)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20that%2C%20you%20can%20use%20XLOOKUP%20using%20the%20default%20first-to-last%20search%20to%20return%20the%20start%20date%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(1%2C%24B2%3A%24O2%2C%24B%241%3A%24O%241%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20will%20look%20in%20cells%20B2%20to%20O2%20for%20a%201%20and%20return%20the%20value%20from%20row%201%20in%20the%20same%20column%20(i.e.%20the%20week%20commencing%20date).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20using%20a%20last-to-first%20search%20to%20return%20the%20end%20week%2C%20then%20add%204%20to%20get%20the%20Friday%20of%20that%20week%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(1%2C%24B2%3A%24O2%2C%24B%241%3A%24O%241%2C%22%22%2C0%2C-1)%2B4%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20will%20look%20backwards%20from%20cell%20O2%20to%20B2%20for%20the%20first%201%20it%20finds%20and%20likewise%20return%20the%20week%20commencing%20date%20from%20row%201%2C%20then%20add%204%20to%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22OwenPrice_0-1594744216468.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F205203iD8BAC89F0811FF45%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22OwenPrice_0-1594744216468.png%22%20alt%3D%22OwenPrice_0-1594744216468.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached%20example.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1521185%22%20slang%3D%22en-US%22%3ERe%3A%20Gantt%20chart%20-%20Pull%20start%2Fend%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1521185%22%20slang%3D%22en-US%22%3E%3CP%3Ebump%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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?

lvircks_0-1594697026393.png

 

3 Replies
Highlighted

bump

Highlighted
Best Response confirmed by lvircks (New Contributor)
Solution

@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:

 

OwenPrice_0-1594743745474.png

(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.

 

OwenPrice_0-1594744216468.png

 

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")
Highlighted

@OwenPrice  Perfect! Thank you so much. And I'm glad you were able to learn something, too