Work around leap year in formula and display appropriate dates (if?)

[dnelley]

I would like a formula that could generate the following outputs

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

 

[RickXL]

Hi,

I am not sure what you are trying to do. No month has a complete five weeks. If it did there could be 60 weeks in a year and not 52 and a bit.

Why do you want to have some dates 7 days apart and some only 1 day apart?

 

[barry houdini]

Hello Dee,

I used this formula for AD2

=IF(AC2=FALSE,EOMONTH(AB2,0)+1,IF(MONTH(AC2+7)=MONTH(AC2),AC2+7,IF(DAY(AC2)=22,FALSE,EOMONTH(AC2,0)+1)))

does that work for you?