ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Functions/formula (https://www.excelbanter.com/excel-worksheet-functions/231654-functions-formula.html)

kandy
Functions/formula
 
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
Functions/formula
 
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


"kandy" wrote:

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
Functions/formula
 
Forgot to mention that Range D8:O8 should be formatted as date.....
--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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


"kandy" wrote:

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.

Jarek Kujawa[_2_]
Functions/formula
 
in D9

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

then drag/copy right

If this post helps please click YES


On 22 Maj, 08:09, Jacob Skaria
wrote:
Forgot to mention that Range D8:O8 should be formatted as date.....
--
If this post helps click Yes
---------------
Jacob Skaria



"Jacob Skaria" wrote:
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

"kandy" wrote:

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.- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Jarek Kujawa[_2_]
Functions/formula
 
or

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

and custom format cells as 0;-0;



On 22 Maj, 11:30, Jarek Kujawa wrote:
in D9

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

then drag/copy right

If this post helps please click YES

On 22 Maj, 08:09, Jacob Skaria
wrote:



Forgot to mention that Range D8:O8 should be formatted as date.....
--
If this post helps click Yes
---------------
Jacob Skaria

"Jacob Skaria" wrote:
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

"kandy" wrote:

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.- Ukryj cytowany tekst -

- Pokaż cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Jarek Kujawa[_2_]
Functions/formula
 
simpler

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



On 22 Maj, 12:23, Jarek Kujawa wrote:
or

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

and custom format cells as 0;-0;

On 22 Maj, 11:30, Jarek Kujawa wrote:



in D9

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

then drag/copy right

If this post helps please click YES

On 22 Maj, 08:09, Jacob Skaria
wrote:

Forgot to mention that Range D8:O8 should be formatted as date.....
--
If this post helps click Yes
---------------
Jacob Skaria

"Jacob Skaria" wrote:
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

"kandy" wrote:

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.- Ukryj cytowany tekst -

- Pokaż cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Kay
Functions/formula
 
THIS FORMULA WORKED PERFECT WHEN I CHANGE THE 3 TO 4! THANK YOU.
--
KK


"Jarek Kujawa" wrote:

simpler

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



On 22 Maj, 12:23, Jarek Kujawa wrote:
or

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

and custom format cells as 0;-0;

On 22 Maj, 11:30, Jarek Kujawa wrote:



in D9

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

then drag/copy right

If this post helps please click YES

On 22 Maj, 08:09, Jacob Skaria
wrote:

Forgot to mention that Range D8:O8 should be formatted as date.....
--
If this post helps click Yes
---------------
Jacob Skaria

"Jacob Skaria" wrote:
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

"kandy" wrote:

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.- Ukryj cytowany tekst -

- Pokaż cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Jarek Kujawa[_2_]
Functions/formula
 
this was my first idea, I switched from 4 to 3 since there is 1
beneath Oct

pls click YES if this helped you



On 22 Maj, 20:51, Kay wrote:
THIS FORMULA WORKED PERFECT WHEN I CHANGE THE 3 TO 4! Â*THANK YOU.
--
KK



"Jarek Kujawa" wrote:
simpler

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

On 22 Maj, 12:23, Jarek Kujawa wrote:
or

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

and custom format cells as 0;-0;

On 22 Maj, 11:30, Jarek Kujawa wrote:

in D9

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

then drag/copy right

If this post helps please click YES

On 22 Maj, 08:09, Jacob Skaria
wrote:

Forgot to mention that Range D8:O8 should be formatted as date......
--
If this post helps click Yes
---------------
Jacob Skaria

"Jacob Skaria" wrote:
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()),MONT H($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

"kandy" wrote:

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.- Ukryj cytowany tekst -

- Pokaż cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -


All times are GMT +1. The time now is 08:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com