SOLVED

Fill Cells Based on Date & Num of Days

Copper Contributor

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.

ForgedinFire5100_1-1704026004822.png

 

10 Replies

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

@Hans Vogelaar 

 

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.

ForgedinFire5100_0-1704041042012.png

 

best response confirmed by ForgedinFire5100 (Copper Contributor)
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).

@Hans Vogelaar 

 

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

 

ForgedinFire5100_0-1704044587457.png

ForgedinFire5100_1-1704044657922.png

 

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

@Hans Vogelaar 

 

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

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

I have messaged you a link to sample workbook.

Thanks.

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

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

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

View solution in original post