Fill Cells Based on Date & Num of Days

[ForgedinFire5100]

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.

[#2]

@ForgedinFire5100 

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.

[#3]

@HansVogelaar 

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.

[#4 · Accepted solution]

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

[#5]

@HansVogelaar 

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

[#6]

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

[#7]

@HansVogelaar 

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

[#8]

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

[#9]

I have messaged you a link to sample workbook.

Thanks.

[#10]

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

[#11]

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.