Forum Discussion
Fill Cells Based on Date & Num of Days
Simple Gantt Chart filling based off of last day of planned work and amount of days required for said work. Need X to be filled in Columns N:T of the related row.
Screenshot attached is example.
If you want a dynamic array formula, it should be
=IF((N$9:R$9>$H11-$G11)*(N$9:R$9<=$H11), "X", "")
(AND returns a single TRUE/FALSE value, not an array).
All this assumes that N9:R9 contains dates (formatted as d-mmm), and that H11 and down contain dates too (formatted as m/d).
In N11:
=IF(AND(N$7>$H11-$G11, N$7<=$H11), "X", "")
where the dates 1-Jan, 2-Jan etc. are assumed to be in N7:T7. Adjust if they are in another row.
Fill to the right to T11.
Thank you for the response. My apologies for not including column and row numbers in original post.
The original formula did not work out, I tried altering to capture the dates. The following formula is what I tried:
=IF(AND(N$9:R$9>$H11-$G11, N$9:R$9<=$H11), "X", "")
This formula doesn't work either.
If you want a dynamic array formula, it should be
=IF((N$9:R$9>$H11-$G11)*(N$9:R$9<=$H11), "X", "")
(AND returns a single TRUE/FALSE value, not an array).
All this assumes that N9:R9 contains dates (formatted as d-mmm), and that H11 and down contain dates too (formatted as m/d).
