if formula help please

Christine81

New Member
Joined
Nov 5, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

1639733712417.png
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
Hi Christine81,

Is this what you want?

Christine81.xlsx
LQRANAOAP
1On Hire DateDeliveryCollectionDelivery to be billed on First InvoiceCollection to be Billed on TerminationTerminated / Continuiing
229-Nov-21$ 85.00$ 66.00$ - Continuing
307-Dec-21$ 250.00$ 77.00$ 250.00 Continuing
408-Dec-21$ -$ -$ - Continuing
508-Dec-21$ 300.00$ 88.00$ 300.00$ 88.00Terminated
601-Jan-22$ 400.00$ 99.00$ -$ 99.00Terminated
Sheet1
Cell Formulas
RangeFormula
AN2:AN6AN2=IF(AND(L2>=EOMONTH(TODAY(),-1)+1,L2<=EOMONTH(TODAY(),0)),Q2,0)
AO2:AO6AO2=IF(AP2="Terminated",R2,"")
 

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
829
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Another option

Book1
LQRANAOAPAQ
1Hire DateDeliveryCollectionDelivery to be BilledCollection to be BilledTerminated/Continuing
211/29/202185850 Continuing
312/7/2021250250250 Continuing
412/8/2021000 Continuing
512/3/2021300300300300Terminated
612/7/2021170170170170Terminated
712/16/2021250250Terminated
8
Sheet3
Cell Formulas
RangeFormula
AN2:AN7AN2=IF(MONTH(TODAY())=MONTH(L2),Q2,0)
AO2:AO7AO2=IF(AP2="Continuing","",R2)
 

Forum statistics

Threads
1,176,123
Messages
5,901,515
Members
434,899
Latest member
powerappsjoker99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top