Forum Discussion

Copper Contributor

Dec 31, 2023

Solved

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

Charting

excel

Formulas and Functions

HansVogelaar
Dec 31, 2023
ForgedinFire5100

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

HansVogelaar

MVP

Dec 31, 2023

ForgedinFire5100 My previous reply contained a typo. I have corrected it; does it work now?

ForgedinFire5100

Copper Contributor

Jan 01, 2024

HansVogelaar

No, all the cells are still blank and returning false.

HansVogelaar
MVP
Jan 01, 2024
ForgedinFire5100

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- ForgedinFire5100
  Copper Contributor
  Jan 01, 2024
  I have messaged you a link to sample workbook.
  
  Thanks.
  - HansVogelaar
    MVP
    Jan 01, 2024
    ForgedinFire5100
    
    Thank you! The problem is that P7 and hence N9:T9 contain dates in 2024, but H11, H12 and H13 contain dates in 2023. If you change the latter to dates in 2024, the formula will work as intended.