Fill a range of cells based on a value in a cell

[scottli]

Hello

I am trying to produce a rudimentary timeline based on the number of days between a start and end date.

I want the result to look like this; I want to fill 7 cells with an "X" automatically.
sample output.jpg

[Jdevil]

In D4, drag right:
=IF(COLUMN(D4)-COLUMN($D4)<$C4,"x","")

[scottli]

Wow. Thank you so much.

[scottli]

Hello again. @Jdevil wondering if you can help again.

I want to be able to fill the cells in with an X starting from a particular cell. Output would look like this.
I embedded your helpful formula into an IF statement to get the right date position - now I'm having trouble figuring out how to start the "x"s in the right cell.
Here is my formula: =IF($A2<=D$1,IF(COLUMN(D2)-COLUMN($D2)<$C2,"x",""),"")
The "x"s start in the right cell but don't fill for the value of what is the number of weeks.

sample output.jpg

[TudyBTH]

Hi,
use this

Please Login or Register  to view this content.

see attached

[scottli]

Thank you!!!

worthy.gif

[scottli]

This is working great, except when the start/end date are in the same week. I would expect to see one "x" but the way the formula is coded, it is resolves to false overall.

sample output.jpg

[TudyBTH]

Hi,
If 5-Aug represents 5-12 August,then is not correct your mark.
Next formula marks the weeks that contain at least one day of task interval.

Please Login or Register  to view this content.

if you want different results, please define more precisely the criteria for marking

[scottli]

Hi TudyBTH. No... 5-Aug represents 1-Aug to 5-Aug. It's the week ending date. 12-Aug represents 6-Aug to 12-Aug.

[TudyBTH]

then try this

Please Login or Register  to view this content.

[scottli]

No

sample output.jpg

[TudyBTH]

Yes
Capture1.JPG

[scottli]

You are RIGHT!! Of course!!!! I ended up deleting the #weeks column before I pasted in your formula . Sorry! And thank you, again.

[scottli]

Hello again. @TudyBTH, I'm hoping you can help again.
If the task start and end dates are the same, the cell is blank. I need it to put a single x in the cell, which I had working - unless the start/end are the same as the "week ending" date.
sample output.jpg

(Also, I had to add logic to check if either start or end were blank, which could happen).

I hope that makes sense.

[TudyBTH]

Hi,
try this

Please Login or Register  to view this content.

[Phuocam]

Try this ...

=IF(AND(WEEKNUM($F4)<=WEEKNUM(S$2),WEEKNUM($G4)>=WEEKNUM(S$2)),"X","")

[scottli]

THANK YOU! (Again)