Functions/formula

[kandy]

start date: 03/26/06( this is in cell k3)


Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun ( these are from cell D8:O8)
1 1 1

I need a formula that can calculate and issue the number 1 every four months
as above; using the start date to issue the first one.

 

[Jacob Skaria]

Dear Kandy

Try the below...in D9 and copy to the other columns upto O9

=IF(OR(MONTH(DATE(YEAR(TODAY()),MONTH($K$3)+4,1))=MONTH(D8),MONTH(DATE(YEAR(TODAY()),MONTH($K$3)+8,1))=MONTH(D8),MONTH(DATE(YEAR(TODAY()),MONTH($K$3)+12,1))=MONTH(D8),MONTH($K$3)=MONTH(D8)),1,"")

I am sure there should be a simpler one...

If this post helps click Yes

 

[Jacob Skaria]

Forgot to mention that Range D8:O8 should be formatted as date.....

 

[Jarek Kujawa]

in D9

=IF(MOD(COLUMN()*3-MONTH($K$3),9),"",1)

then drag/copy right

If this post helps please click YES

 

[Jarek Kujawa]

or

=IF(MOD(COLUMN()*3-MONTH($K$3),9),,1)

and custom format cells as 0;-0;

 

[Jarek Kujawa]

simpler

=IF(MOD(COLUMN()-MONTH($K$3)-1,3),,1)

or

=IF(MOD(COLUMN()*3-MONTH($K$3),9),,1)

and custom format cells as 0;-0;






- Pokaż cytowany tekst -

 

[Kay]

THIS FORMULA WORKED PERFECT WHEN I CHANGE THE 3 TO 4! THANK YOU.

 

[Jarek Kujawa]

this was my first idea, I switched from 4 to 3 since there is 1
beneath Oct

pls click YES if this helped you