I would like a formula that could generate the following outputs
<tbody>
</tbody>
I am having a hard time creating a formula the would produce the above
I have tried to create the above using nested if statements -- but after a few cells my formula no longer gives valid results
here's what I had tried to use:
IF(AC158=FALSE, EOMONTH(AB158,0)+1, IF(MONTH(AC158+7)=MONTH(AC158), AC158+7, IF(((EOMONTH(AB158,-1)+1)+(4*7))=(AC158+7), EOMONTH(AB158,0)+1, FALSE))) == cell AD158
the formula is dragged to the right
this one almost works but i get too many or too few empty("FALSE") cells than I want
<tbody>
</tbody>
here's another one
IF(MONTH(AC174+7)=MONTH(AC174), AC174+7, IF(((EOMONTH(AB174,-1)+1)+(4*7))=(AC174+7), EOMONTH(AB174,0)+1, IF(AC174=FALSE, EOMONTH(AB174,0)+1, FALSE))) == cell AD174
<tbody>
</tbody>
another fail:
IF(AC135=FALSE, (EOMONTH(AB135,0))+1, IF(MONTH(AC135+7)=MONTH(AC135), AC135+7, FALSE))== cell AD174
<tbody>
</tbody>
I'm trying to display five weeks for every month and since february doesn't consistently have five weeks I need my formula to recognize this,
ultimately i would like to generate a gantt table around these outputs
any insight/assistance is welcomed
Thanks :Dee
AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | |
ex:1 | 2/1/14 | 2/8/14 | 2/15/14 | 2/22/14 | 3/1/14 | 3/8/14 | 3/15/14 | 3/22/14 | 3/29/14 | 4/1/14 | 4/8/14 | |||
ex:2 | 2/1/16 | 2/8/16 | 2/15/16 | 2/22/16 | 2/29/16 | 3/1/16 | 3/8/16 | 3/15/16 | 3/22/16 | 3/29/16 | 4/1/16 | 4/8/16 | ||
<tbody>
</tbody>
I am having a hard time creating a formula the would produce the above
I have tried to create the above using nested if statements -- but after a few cells my formula no longer gives valid results
here's what I had tried to use:
IF(AC158=FALSE, EOMONTH(AB158,0)+1, IF(MONTH(AC158+7)=MONTH(AC158), AC158+7, IF(((EOMONTH(AB158,-1)+1)+(4*7))=(AC158+7), EOMONTH(AB158,0)+1, FALSE))) == cell AD158
the formula is dragged to the right
this one almost works but i get too many or too few empty("FALSE") cells than I want
AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | ||
ex:1 | 158 | 2/1/14 | 2/8/14 | 2/15/14 | 2/22/14 | 3/1/14 | 3/8/14 | 3/15/14 | 3/22/14 | 3/29/14 | 4/1/16 | 4/8/14 | 4/15/16 | ||
ex:2 | 158 | 2/1/16 | 2/8/16 | 2/15/16 | 2/22/16 | 2/29/16 | 3/1/16 | 3/8/16 | 3/15/16 | 3/22/16 | 3/29/16 | 4/1/16 | |||
<tbody>
</tbody>
here's another one
IF(MONTH(AC174+7)=MONTH(AC174), AC174+7, IF(((EOMONTH(AB174,-1)+1)+(4*7))=(AC174+7), EOMONTH(AB174,0)+1, IF(AC174=FALSE, EOMONTH(AB174,0)+1, FALSE))) == cell AD174
AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | ||
ex:1 | 174 | 2/1/14 | 2/8/14 | 2/15/14 | 2/22/14 | 3/1/14 | 3/8/14 | 3/15/14 | 3/22/14 | 3/29/14 | 1/7/00 | 1/14/00 | 1/21/00 | ||
ex:2 | 174 | 2/1/16 | 2/8/16 | 2/15/16 | 2/22/16 | 2/29/16 | 1/7/00 | 1/14/00 | 1/28/00 | #num | #num | #num | #num | ||
<tbody>
</tbody>
another fail:
IF(AC135=FALSE, (EOMONTH(AB135,0))+1, IF(MONTH(AC135+7)=MONTH(AC135), AC135+7, FALSE))== cell AD174
AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | ||
ex:1 | 135 | 2/1/14 | 2/8/14 | 2/15/14 | 2/22/14 | 3/1/14 | 3/8/14 | 3/15/14 | 3/22/14 | 3/29/14 | 4/1/14 | 4/8/14 | |||
ex:2 | 135 | 2/1/16 | 2/8/16 | 2/15/16 | 2/22/16 | 2/29/16 | 3/1/16 | 3/8/16 | 3/15/16 | 3/22/16 | 3/29/16 | 4/1/16 | |||
<tbody>
</tbody>
I'm trying to display five weeks for every month and since february doesn't consistently have five weeks I need my formula to recognize this,
ultimately i would like to generate a gantt table around these outputs
any insight/assistance is welcomed
Thanks :Dee