Skip to main content

Formula Help - How to pull the date from a record, with conditions?

  • December 5, 2019
  • 2 replies
  • 21 views
D

I’ve built a “Communications Log” table to track the date and type of communication (i.e. email, phone, thank you note, etc.) each time we communicate with a customer (linked to a record in another table). I’m trying to build a formula to to show me the most recent date a thank you note was sent. I know I can use the rollup function and "Max(values) from the date field, but I only want to use the date if the communication type was “thank you note.”

My basic formula would be something like this IF ({communication type} = “Thank You Note”, {date}, " "). Then I could rollup the dates in this field, but I can’t get the formula to work. Any ideas?

Thanks,
Dave

    This topic has been closed for replies.

    2 replies

    A
    Forum|alt.badge.img+19
    • AlliAlosa
    • Inspiring
    • December 5, 2019

    Hi @David_Eastman, welcome to the community!

    You are on the right track. I have a hunch that your problem is being caused by the " " at the end of your formula.

    What’s happening here is that if {communication type} is NOT “Thank You Note”, you now have a single space in that field instead of nothing. This causes Airtable to see the field as a string rather than a date; causing MAX(values) to error when rolling it up.

    Try getting rid of that space at the end. IF formulas in Airtable will inherently leave the value blank if the third statement is omitted.

    IF({communication type} = "Thank You Note", {date})

    You should be able to roll up the field as a date after that :slightly_smiling_face:

    Hope this helps!

      D
      • David_Eastman
      • Author
      • New Participant
      • December 6, 2019

      Hi @David_Eastman, welcome to the community!

      You are on the right track. I have a hunch that your problem is being caused by the " " at the end of your formula.

      What’s happening here is that if {communication type} is NOT “Thank You Note”, you now have a single space in that field instead of nothing. This causes Airtable to see the field as a string rather than a date; causing MAX(values) to error when rolling it up.

      Try getting rid of that space at the end. IF formulas in Airtable will inherently leave the value blank if the third statement is omitted.

      IF({communication type} = "Thank You Note", {date})

      You should be able to roll up the field as a date after that :slightly_smiling_face:

      Hope this helps!


      Worked perfectly, thank you! I was under the impression I needed the “else” statement, even if blank.

        Terms & ConditionsAccessibility statement