Forum Discussion
ForgedinFire5100
Dec 31, 2023Copper Contributor
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. ...
- Dec 31, 2023
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).
ForgedinFire5100
Dec 31, 2023Copper Contributor
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.
HansVogelaar
Dec 31, 2023MVP
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).
- ForgedinFire5100Dec 31, 2023Copper Contributor
Thank you for your time Hans.
It appears that the cells are appearing as false (I replaced the blank with EX to more easily see how formula is working).
- HansVogelaarJan 01, 2024MVP
ForgedinFire5100 My previous reply contained a typo. I have corrected it; does it work now?
- ForgedinFire5100Jan 01, 2024Copper Contributor