Formula IF nested using "and" what I am doing wrong?

I have been using this formula, and it works:

IF({Company (from Distribution Locations) (from Invoice 2)}="Key Fresh 8890",{Cost *1 (from Products 2)}, IF({Company (from Distribution Locations) (from Invoice 2)}="Marathon 16050", {Cost *4 (from Products 3)}, IF({Company (from Distribution Locations) (from Invoice 2)}="Chevron 10255", {Cost *5 (from Products 4)},IF({Company (from Distribution Locations) (from Invoice 2)}="Chevron 3251", {Cost *2 (from Products 5)}, IF({Company (from Distribution Locations) (from Invoice 2)}="Marathon 5", {Cost *2 (from Products 5)},

{Cost (from Products 2) 2})))))

Now I want to add another nested IF; however, with "and," it is not working for me.

I want to include the following: if(AND({Company (from Distribution Locations) (from Invoice 2)}="Chevron 3100",{Date (from Invoice)}<"03/19/2024"), {Cost *4 (from Products 3)}, {Cost (from Products 2) 2})

When I add this last part, it gives me an error. I tried two methods of putting this addition at the end and then trying putting it as part of a nested IF in the original, but neither works.

Would it be how I format the date? missing any syntax?

Page 1 / 1

The date as you have written it would definitely cause issues. Airtable interprets anything wrapped in quotes as a string. You’re trying to compare that string to a date field, which is a different data type. It’s important to make sure the things you are comparing are of the same type.

To force Airtable to see the date as a date, try wrapping the date in DATETIME_PARSE().

DATETIME_PARSE('2024-03-19')

I think that should do the trick, but it’s hard to say for sure if that’s the only problem without seeing the whole formula in full 😊

Additionally, if the date comparison still doesn’t work after the date conversion, then try turning to the date comparison functions like IS_AFTER() or IS_BEFORE(). More details on the formula page here: https://support.airtable.com//docs/formula-field-reference

@AlliAlosa and @ScottWorld, thank you for your responses. @AlliAlosa, the formula was accepted, but it affects all data. I am using the IF AND formula, so I want only data affected after a certain date.

Here is the complete formula:

IF(AND({Company (from Distribution Locations) (from Invoice 2)}="Chevron 3100",{Date (from Invoice)}<DATETIME_PARSE('2024-03-19')), {Cost *4 (from Products 3)},

{Cost (from Products 2) 2}))))))

It seems the "<DATETIME_PARSE('2024-03-19')" is not being recognized. However, it is accepted as part of the formula. I want the formula to consider the date after March 19, 2024.

With the DATETIME_PARSE function, you are required to specify an input format, as it mentions on the formula page I linked to above.

So for you, it would look like this:
DATETIME_PARSE('2024-03-19','YYYY-MM-DD')

p.s. If your company has a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld

It's not recommended to just compare Date with "<",">". Use IS_BEFORE, IS_AFTER instead. Try to write the date in usual ISO format "2024-03-19" (YYYY-MM-DD), without any DATETIME_PARSE. My opinion is that ISO format is the 'most correct' in such cases, because it can be sorted as usual string.

Reformat your previous working formula with SWITCH, and use it in similar cases. It will make easier to construct more complex formulas with it. Even when you combine it in "nested IF", it's better to not be trapped in a chaos of many IFs

I agree with @Alexey_Gusev - a SWITCH() statement would be much cleaner.

Also, I just tested and IS_AFTER() / IS_BEFORE do work without DATETIME_PARSE(), which is wild - I never would have thought.

Taking everything together, I think the below should work for you. All I did was format the SWITCH() statement, have not tested the full thing, but I hope this at least gets you started.

SWITCH(


  {Company (from Distribution Locations) (from Invoice 2)},


  "Key Fresh 8890", {Cost *1 (from Products 2)}, 


  "Marathon 16050", {Cost *4 (from Products 3)},


  "Chevron 10255", {Cost *5 (from Products 4)},


  "Chevron 3251", {Cost *2 (from Products 5)},


  "Marathon 5", {Cost *2 (from Products 5)},


  "Chevron 3100",


  IF(


    IS_AFTER(


      '2024-03-19',


      {Date (from Invoice)}


    ),


    {Cost *4 (from Products 3)},


    {Cost (from Products 2) 2}


  ),


  {Cost (from Products 2) 2}


)

I agree with @Alexey_Gusev - a SWITCH() statement would be much cleaner.

Also, I just tested and IS_AFTER() / IS_BEFORE do work without DATETIME_PARSE(), which is wild - I never would have thought.

Taking everything together, I think the below should work for you. All I did was format the SWITCH() statement, have not tested the full thing, but I hope this at least gets you started.

SWITCH(


  {Company (from Distribution Locations) (from Invoice 2)},


  "Key Fresh 8890", {Cost *1 (from Products 2)}, 


  "Marathon 16050", {Cost *4 (from Products 3)},


  "Chevron 10255", {Cost *5 (from Products 4)},


  "Chevron 3251", {Cost *2 (from Products 5)},


  "Marathon 5", {Cost *2 (from Products 5)},


  "Chevron 3100",


  IF(


    IS_AFTER(


      '2024-03-19',


      {Date (from Invoice)}


    ),


    {Cost *4 (from Products 3)},


    {Cost (from Products 2) 2}


  ),


  {Cost (from Products 2) 2}


)

Hi,
I think you missed a comma after '2024-03-19'

lol sure did! Thank you!

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded