| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula behind functions | Excel Worksheet Functions | |||
| Add several functions in one formula | Excel Worksheet Functions | |||
| How to convert cell formula functions to code functions | Excel Discussion (Misc queries) | |||
| formula/functions for average and if functions | Excel Worksheet Functions | |||
| efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Linear Mode
