SOLVED

# Fill Cells Based on Date & Num of Days

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

10 Replies

# Re: Fill Cells Based on Date & Days

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.

# Re: Fill Cells Based on Date & Days

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.

best response confirmed by ForgedinFire5100 (Copper Contributor)
Solution

# Re: Fill Cells Based on Date & Days

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

# Re: Fill Cells Based on Date & Days

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

# Re: Fill Cells Based on Date & Days

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

# Re: Fill Cells Based on Date & Days

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

# Re: Fill Cells Based on Date & Days

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?

# Re: Fill Cells Based on Date & Days

I have messaged you a link to sample workbook.

Thanks.

# Re: Fill Cells Based on Date & Days

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.

# Re: Fill Cells Based on Date & Days

Wow that's embarrassing on my part, formula works as intended now.

You're an absolute Saint! Thank you very very much for the help.
1 best response

Accepted Solutions
best response confirmed by ForgedinFire5100 (Copper Contributor)
Solution

# Re: Fill Cells Based on Date & Days

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