Calculating every Quarter or Half year from today

[alabatusa]

Hi All

I am trying to calculate given a varying starting day, the fall of every quarter or half year.

e.g. 1

month= 1 (Jan)
Type = quarter
Next quarter = 4 (april)

Similarly
e.g. 2
Date = 1 Feb
Type = quarter
Next quarter = 5( may)

or
Similarly
Date = 1 Feb
Type = semi annual
Next quarter = 7 (july)

Basically i need a formula given starting month to determine if the cell given falls on quarter on semi annual anniversary

such that over a calender year i would get the following for example 2.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Flag[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

and similarly for EG 3. would return a 1 for month of Feb and July


My inputs are Month begin and quarterly/semi annual input as (3,6)

 

[iggy_]

Try something like this.

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][th]

M​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Date[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Type[/td][td]quarter[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Next Quarter[/td][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Month[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][td]

7​

[/td][td]

8​

[/td][td]

9​

[/td][td]

10​

[/td][td]

11​

[/td][td]

12​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Flag[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet11[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

=IF(B2="quarter",B1+3,IF(B2="semi",B1+6,"check type"))​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet11[/td][/tr][/table]
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

=IF($B$2="quarter",IF(MOD(B$5-$B$1,3)=0,1,0),IF($B$2="semi",IF(MOD(B$5-$B$1,6)=0,1,0),"check type"))​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet11[/td][/tr][/table]