Jun 30 2022 05:23 AM
Hello! I am hoping someone can help me write a complicated formula.
I have the following columns:
F: Payment Date
D: Grant Committed Date
O: Payment Type
J: Payment Amount
I am trying to write a formula that will look at these columns and do the following:
IF
Payment Date is in 2021
Grant Committed Date is prior to 2021
Payment Type is "Conditional" or "Conditions Satisfied"
THEN
Display Payment Amount
IF
Conditions above are not met, leave cell blank
This is what I have right now but I'm doing something wrong:
=IF(AND(F2>=DATEVALUE("1/1/2021"),F2<=DATEVALUE("12/31/2021"),(OR,O2,"Conditional","Conditions Satsified"),AND(D2<datevalue("1/1/2021"))),J2,"")
Jun 30 2022 05:46 AM
Solution=IF(AND(YEAR(F2)=2021,YEAR(D2)<2021,OR(O2="Conditional",O2="Conditions Satisfied")),J2,"")
You can try this formula which seems to work in my sheet.
Jul 07 2022 07:32 AM
@OliverScheurich thank you SO much for your reply! This formula worked perfectly and you taught me about the YEAR function which is much neater than what I had been using. I improved my entire spreadsheet thanks to this. This may have seemed obvious to you but it seems like magic to me and I really appreciate you taking the time to respond and help me.
Jul 11 2022 06:42 PM
@OliverScheurich hello! I'm still toiling away on this spreadsheet, trying to get all my formulas set up for future years. The column I'm working on now has the following conditions:
Column D: Grant Committed Date
Column F: Grant Payment Date
Column J: Payment Amount
Column O: Payment Type
IF
Grant Committed Date prior to 2021
Payment type NOT "conditional"
Payment type NOT "conditions satisfied" IF payment date is after 2020 OR payment date is blank
THEN
Insert Payment Amount
IF NOT
Insert blank
This is what I have now:
=IF(AND(YEAR(D6)<2021,O6<>"Conditional",(OR(O6="Conditions Satisfied",AND(OR(YEAR(F6)>2020,F6=""))))),J6,"")
It is excluding the conditional payments correctly but not the ones marked conditions satisfied that were paid after 2020.
Again, I'm sure this is something obvious I'm doing wrong, but I would appreciate your guidance! Thank you!
Jul 12 2022 06:58 AM
=IF(AND(YEAR(D6)<2021,O6="Conditions Satisfied",YEAR(F6)>2020),"",IF(AND(YEAR(D6)<2021,O6<>"Conditional",(OR(O6="Conditions Satisfied",AND(OR(YEAR(F6)>2020,F6=""))))),J6,""))
Does this formula return your expected result? Unfortunately i'm not sure if i correctly understand what you want to do. As your formula excludes the conditional payments correctly i only added an additional IF statement for conditions satisfied that were paid after 2020.
Jun 30 2022 05:46 AM
Solution=IF(AND(YEAR(F2)=2021,YEAR(D2)<2021,OR(O2="Conditional",O2="Conditions Satisfied")),J2,"")
You can try this formula which seems to work in my sheet.