if formula help please

[Christine81]

hi guys happy Friday, looking for some help please here is a chopped down screen shot of my data. I'm trying to work out the formula so that if the on hire date is this month delivery to be billed on first invoice returns the value in the delivery cell but if it is not the current month it returns zero value

also if the terminated/continuing says terminated i want collection to be billed on termination to return the collection value and if it says continuing for it to be blank

 

[Toadstool]

Hi Christine81,

Is this what you want?

Christine81.xlsx
	L					Q	R																						AN	AO	AP
1	On Hire Date					Delivery	Collection																						Delivery to be billed on First Invoice	Collection to be Billed on Termination	Terminated / Continuiing
2	29-Nov-21					$ 85.00	$ 66.00																						$ -		Continuing
3	07-Dec-21					$ 250.00	$ 77.00																						$ 250.00		Continuing
4	08-Dec-21					$ -	$ -																						$ -		Continuing
5	08-Dec-21					$ 300.00	$ 88.00																						$ 300.00	$ 88.00	Terminated
6	01-Jan-22					$ 400.00	$ 99.00																						$ -	$ 99.00	Terminated
Sheet1

Cell Formulas
Range	Formula
AN2:AN6	AN2	=IF(AND(L2>=EOMONTH(TODAY(),-1)+1,L2<=EOMONTH(TODAY(),0)),Q2,0)
AO2:AO6	AO2	=IF(AP2="Terminated",R2,"")

 

[Sufiyan97]

Another option

Book1
	L					Q	R																						AN	AO	AP	AQ
1	Hire Date					Delivery	Collection																						Delivery to be Billed	Collection to be Billed	Terminated/Continuing
2	11/29/2021					85	85																						0		Continuing
3	12/7/2021					250	250																						250		Continuing
4	12/8/2021					0	0																						0		Continuing
5	12/3/2021					300	300																						300	300	Terminated
6	12/7/2021					170	170																						170	170	Terminated
7	12/16/2021					25	0																						25	0	Terminated
8
Sheet3

Cell Formulas
Range	Formula
AN2:AN7	AN2	=IF(MONTH(TODAY())=MONTH(L2),Q2,0)
AO2:AO7	AO2	=IF(AP2="Continuing","",R2)

 

[Christine81]

thank you so much guys

 

[Sufiyan97]

You're Welcome!