Populate Based on Month and Year

[kumara_faith]

Hi,

I have a table as below:

Book2
	C	D	E	F	G	H
2	Date	Monthly Bonus	Yearly Bonus		Monthly Bonus	500
3	14/1/2020	500			Yearly Bonus	2000
4	15/1/2020
5	16/1/2020
6	17/1/2020
7	18/1/2020
8	19/1/2020
9	20/1/2020
10	21/1/2020
11	22/1/2020
12	23/1/2020
13	5/2/2020	500
14	6/2/2020
15	7/2/2020
16	8/2/2020
17	9/2/2020
18	10/2/2020
19	15/3/2020	500
20	16/3/2020
21	17/3/2020
22	18/3/2020
23	19/3/2020
24	20/3/2020
25	21/3/2020
26	4/1/2021		2000
27	5/1/2021
28	6/1/2021
29	7/1/2021
30	8/1/2021
Sheet1

I need to insert the monthly bonus of USD500 at the earliest date of the month and the yearly bonus of USD 2000 at the earliest date of the following year. I am working on a list of a few years. Is there a formula that I could use to automate this ? Appreciate all the help.

 

[Fluff]

Why does the 1st in Jan 2020 have a monthly bonus & not a yearly one, whereas the 1st date in Jan 21 is the otherway round?

 

[etaf]

EDIT
Just seen the reply from Fluff as I hit the post button

so ignore below

i have used a couple of IF () to get the info
just not the 1st, will work on or someone else may have a better solution

Book1
	A	B	C	D	E	F
1	Date	Monthly Bonus	Yearly Bonus		Monthly Bonus	500
2	1/14/20	#VALUE!	#VALUE!		Yearly Bonus	2000
3	1/15/20
4	1/16/20
5	1/17/20
6	1/18/20
7	1/19/20
8	1/20/20
9	1/21/20
10	1/22/20
11	1/23/20
12	2/5/20	500
13	2/6/20
14	2/7/20
15	2/8/20
16	2/9/20
17	2/10/20
18	3/15/20	500
19	3/16/20
20	3/17/20
21	3/18/20
22	3/19/20
23	3/20/20
24	3/21/20
25	1/4/21		2000
26	1/5/21
27	1/6/21
28	1/7/21
29	1/8/21
Sheet1

Cell Formulas
Range	Formula
B2:B29	B2	=IF(AND(YEAR(A1)=YEAR(A2),MONTH(A2)<>MONTH(A1)),$F$1,"")
C2:C29	C2	=IF(AND(YEAR(A1)<>YEAR(A2)),$F$2,"")

 

[Toadstool]

Hi Kamara_Faith,

I'm assuming 14 Jan 20 should also have a yearly bonus as it's the first date of 2020.

Kumara_Faith.xlsx
	C	D	E	F	G	H
2	Date	Monthly Bonus	Yearly Bonus		Monthly Bonus	500
3	14-Jan-20	500	2000		Yearly Bonus	2000
4	15-Jan-20
5	16-Jan-20
6	17-Jan-20
7	18-Jan-20
8	19-Jan-20
9	20-Jan-20
10	21-Jan-20
11	22-Jan-20
12	23-Jan-20
13	05-Feb-20	500
14	06-Feb-20
15	07-Feb-20
16	08-Feb-20
17	09-Feb-20
18	10-Feb-20
19	15-Mar-20	500
20	16-Mar-20
21	17-Mar-20
22	18-Mar-20
23	19-Mar-20
24	20-Mar-20
25	21-Mar-20
26	04-Jan-21	500	2000
27	05-Jan-21
28	06-Jan-21
29	07-Jan-21
30	08-Jan-21
Sheet1

Cell Formulas
Range	Formula
D3:D30	D3	=IF(C3=AGGREGATE(15,6,$C$3:$C$30/(($C$3:$C$30>=DATE(YEAR(C3),MONTH(C3),1))*($C$3:$C$30<=EOMONTH(C3,0))),1),$H$2,"")
E3:E30	E3	=IF(C3=AGGREGATE(15,6,$C$3:$C$30/(($C$3:$C$30>=DATE(YEAR(C3),1,1))*($C$3:$C$30<=DATE(YEAR(C3),12,31))),1),$H$3,"")

 

[Martunis99]

One question: the first cell is 14/01 but is given a monthly bonus and not yearly. Shouldn't this get an yearly bonus? As it is the earliest date of the year 2020?

Either way here is my suggestion. This should work:

Monthly Bonus
=IF(SUMPRODUCT(1*(MONTH($A$2:A2)=MONTH(A2)))>1,"",500)

Yearly Bonus
=IF(SUMPRODUCT(1*(YEAR($A$2:A2)=YEAR(A2)))>1,"",2000)

Much like Toadstool I have also assumed that the turn of the new year gets both a montly and yearly salary.

 

[Fluff]

Another possible option

+Fluff 1.xlsm
	C	D	E	F	G	H
1
2	Date	Monthly Bonus	Yearly Bonus		Monthly Bonus	500
3	14/01/2020	500	2000		Yearly Bonus	2000
4	15/01/2020
5	16/01/2020
6	17/01/2020
7	18/01/2020
8	19/01/2020
9	20/01/2020
10	21/01/2020
11	22/01/2020
12	23/01/2020
13	05/02/2020	500
14	06/02/2020
15	07/02/2020
16	08/02/2020
17	09/02/2020
18	10/02/2020
19	15/03/2020	500
20	16/03/2020
21	17/03/2020
22	18/03/2020
23	19/03/2020
24	20/03/2020
25	21/03/2020
26	04/01/2021	500	2000
27	05/01/2021
28	06/01/2021
29	07/01/2021
30	08/01/2021
31
Main

Cell Formulas
Range	Formula
D3:D30	D3	=IF((YEAR(C3:C30)>IFERROR(YEAR(C2:C29),0))+(MONTH(C3:C30)>IFERROR(MONTH(C2:C29),0)),H2,"")
E3:E30	E3	=IF(YEAR(C3:C30)>IFERROR(YEAR(C2:C29),0),H3,"")
Dynamic array formulas.

 

[kumara_faith]

Hi Fluff, Martunis, Toadstool and etaf,

My apologies. Yes, January 2020 and January 2021 should have both monthly bonus and yearly bonus.

Thank you for your valuable time and patience. Appreciate all the solutions and it worked. Have a blessed day ahead everyone.?

 

[Fluff]

Glad we could help & thanks for the feedback.