Re: Complex IF formula - sales activities (past/pending, today, programmed, no activity)
So I recently became an Airtabler :slightly_smiling_face: (if it can be called that way). At my company we chose Airttable to unify all of the information from customers, invoicing, etc, so teams con access the same data. We stoped using Pipedrive to program Airtable to do all the things a CRM can. One of the things that Pipedrive can do and Im trying to achive here is to create an activity (meeting, phone call, email, etc) and forget about it because it will give me the “Status” of the business automatically.
It would show a red dot when an activity was past due, a yellow dot when it didn’t had any activity programmed, a green one when an activity is for today, and a grey dot when it has an activity programmed for a future day.
My sales airtable is organized like this (screenshots below with sometimes spanish titles). 4 tables. The main one is called organizations and the other 3 are emails (correos), meetings (reuniones) and calls (llamadas). The last three are the sales activities that have to be related to the “Status of an activity” of the organization/business.
In the table Organizations I created a field called “Status of the activity” with the 4 States that I previously mentioned. see screenshot here:
Then I created a field right next to it called “formula activities” where im trying to do an IF formula that basically says: If this organization doesn’t have any programmed activity for today or the future then “no activity” (yellow). If this organization has a programmed activity for tomorrow and beyond then “programmed activity” (grey). If this organization has an activity for today then “Activity for today” (green), and finally, If this organization has an activity past due from yesterday or before that, then “past activity” (red).
One of the hard things is pulling field data from a table into the formula. I have been trying to do this for a long time now and have not been successful. If someone can help me with this formula would be greatly appreciated! Thanks in advance and sorry for my grammar :grinning_face_with_smiling_eyes:
@Simon_Chamorro To answer the first question: If you wouldn’t mind using emoji’s in your formula, you could achieve something like this:
The formula used is:
IF(Activity="",“ :yellow_heart: No activity”,IF(DATETIME_DIFF(Date,TODAY(),‘d’)<0,“ 
Done”,IF(DATETIME_DIFF(Date,TODAY(),‘d’)=0,“ :green_heart: Today”,IF(DATETIME_DIFF(Date,TODAY(),‘d’)>0,“ :purple_heart: In the future”,Blank()))))
I can’t find dots in the emoji list, so hearts could be an option.
