Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,941 Topics
  • 18,238 Replies
5941 Topics
Z
zeremKnown Participant
posted in Formulas
how can i use "If" with checkbox?

Hey friendsI want to create a star rating in the system based on V in existing checkboxes.That is, if you watched video 1 - one star.If you also watched video 2 - 2 starsAnd so on.How can you create such a thing?That is, an IF that depends on a checkbox.Thank you! 

434
Z
W
Wendy_YelsikInspiring
posted in Formulas
Grand Total of Rows

I need to export a grand total number from Airtable to import into our payroll software as a csv.   The table is setup by an employee and that employee has a new row for each month of the year with a number in it.   I know that airtable sums those columns, but I don't know how to export the grand total by employee.   I need the grand total of the {Dollar$} field exported.  How can I do this?   Thanks. Wendy

184
W
S
Sara11Inspiring
asked in Formulas
How "Stop" the filter on lookup on just a specific view?

Hi, I'm working on multiple views and 2 tables.When I filter a lookup/rollup, those filters are applied to all the views of that table. Is there any way I can "stop" the filter to only one specific view?I would like to avoid creating multiple tables for each filter.Thank you 🙏

70
S
P
paulmtilleyNew Participant
posted in Formulas
Calculate start and end times

Hello! I am new to using Airtable and am excited to learn about its capabilities. I am creating an event program and I would like to enter the start and end dates of each part of the program. Is it possible to automatically update the end time of each event based on the duration that I input? For example, if I enter a start time of 6pm and a talk that lasts 6 minutes, I would like the end time to be updated to 6:06pm. I would like to use this feature to see if any events are going over their allotted time. Can you help me with this?

534
J
F
FranckNew Participant
asked in Formulas
Sumup formula or Script

Hello alls,I have a sum up column as shown in the picture above. Actually, I use excell for the calculation (I copy the value column in excell, I make the calculation of the sumup column in excell and then I copy back to the airtable sumup column). Hence, I'd like to know if it exist any function or script that could do this calculation directly in airtable. Thx 

150
F
M
mrwinter11Participating Frequently
asked in Formulas
Display time differences as Weeks, Days

Hi,I'm trying to work out how to display the difference between two dates as weeks, then days.As an example, one of our crew will be away for 9 weeks and 3 days. It is difficult for them to work out how long that is if it's only displayed in days (66) and in weeks it's not accurate enough.If I useDATETIME_DIFF({End Date},{Start Date},'w')Then anything less than one week displays as 0.0, and the extra days are not accounted for.Ideally I'd like to be able to display "9w, 3d".Any help working this out would be greatly appreciated.

490
M
P
Peter3New Participant
posted in Formulas
Questionnaire - transforming raw data into standardised one

Hi, I will be very grateful if you can suggest a solution to the problem. I am doing a lot of questionnaire surveys, the results of which need to be standardized. I don't know how I should do it using Airtable. Currently, I have created two tables. Table 1 shows the questionnaire survey results (ws_score) and table 2 shows the standardized results. What should I do (what formula?) to substitute the standardized score from table 2 (sten) into table 1 (sten_number)?  

43
TheTimeSavingCo
J
John_B2Inspiring
posted in Formulas
Today() should be today?

It is currently the 18th Dec in NZ. The field I have called TodayNZ is a calc "Today()". It is formatted as NZ time zone. Is  there any reason why it comes up as the 17th Dec? 

114
TheTimeSavingCo
M
MD_2241New Participant
asked in Formulas
Auto Generate Random(ish) Group Assignments

I'm trying to randomly assign one of four Group IDs (AlphaGroup, BetaGroup, GammaGroup, DeltaGroup) to all existing/new records in my database whether they're imported in bulk or added manually. I've been using this formula—however since some of my records will be imported in bulk, their created time remains the same and gives them all the same Group ID). I also have First Name, Last Name, Email, and other fields that could potentially be useful here. Any idea how to address this? Thanks!  IF( SECOND( CREATED_TIME() ) < 15, "AlphaGroup", IF( SECOND( CREATED_TIME() ) < 30, "BetaGroup", IF( SECOND( CREATED_TIME() ) < 45, "GammaGroup", "DeltaGroup" ))) 

90
TheTimeSavingCo
P
plyskeInspiring
posted in Formulas
Need help with a formula for choosing between 2 options

Hello everyone! I've made a table that works as a 'Recurring tasks' table. When someone finishes a job, they'll mark the job as done by using a checkmark (se picture). The task can either be a daily task or a weekly task. If it's daily the 'Daglig' checkmark will be used, if it's weekly, the 'Ugentlig' checkmark will be used. Depending on what's chosen the table will find the right "next due date". The thing is however: the table is grouped by the field 'Hyppighed' (how often a task is being made), so when a task is being checked, it will be moved to an 'Empty' group since 'Hyppighed' isn't automatically filled.  My question is: What should a formula look like in order for it to automatically fill in either 'daily' or 'Weekly' depending on what kind of task it is? I want this in order for the tasks to move to the right group again. Hope it makes sense.  

128
P
M
MythicNew Participant
posted in Formulas
Linking Outlook Calendar meetings to automatically update Last Meeting field in another table

I've been trying to automate my CRM to automatically update the date of my last interaction with someone by linking my Outlook calendar meetings to records of people in my "People" table and then have the most recent meeting date show up in the People table under "Last Interaction".I tried adding a People column in the Outlook calendar table linked to the People Table with a lookup record to search for the contact in the attendees list based on their Primary Email but it's not pulling the people.Ultimately, I am trying to have the Calendar table search for the attendees based on their email in the People table, then update the records for the associated contacts column for "Last Interaction" in the People table.Any ideas on how to link the tables and automatically update the date of our last meeting without having to manually add the attendees?

133
TheTimeSavingCo
P
plyskeInspiring
asked in Formulas
Create Button with unique URL in same table

Hello everybody! I am trying to create some buttons in a table with a lot of different information. It looks like this:   The issue is: right now every button leads to the same URL. I want each (or at least) some of the buttons to direct to other URLs within the same table. Isn't that possible?

620
P
A
Abdullah_YukselInspiring
posted in Formulas
The formula which finds the last working day of the year

I was setting a "Happy Christmas" automation. I could have set the automation time trigger to "YEAR-12-31", but using the last working day as a trigger is better. Firstly I searched on the community for this kind of formula. I could not find. So, I wanted to share with you.The weekday number of the 29th day is important, since if it is friday, this day should be trigger date, since 31th is sunday, etc. Also, if you have a better formula or find a problem in my formula, please share your comments. IF(AND(WEEKDAY(YEAR(TODAY()) & "-12-29") = 5, YEAR(TODAY()) & "-12-29" = DATETIME_FORMAT(TODAY(),"YYYY-MM-DD")),1, IF(AND(WEEKDAY(YEAR(TODAY()) & "-12-29") = 4, YEAR(TODAY()) & "-12-30" = DATETIME_FORMAT(TODAY(),"YYYY-MM-DD")),1, IF(YEAR(TODAY()) & "-12-31" = TODAY(),1,0)))   

94
A
D
Denis_SNew Participant
posted in Formulas
Link tables with limit records to a view option not working

Hello, from a booking days main table, I create a view for records filtered on the next day called D+1. Then, I create a "Tomorrow" table with one single row linked to the main table to make a summary by rollups of certain datas : clients list, quantities, and so on. For this link field, I choose the option "limit selected records to a view", which is the D+1 view. So I guess I’m just going to summarize the D+1 view filtered records, but... No ! The rollups summarize the ENTIRE main table without taking into account the view filter !I am not looking for a solution because I have already found others but I do not understand what the "limit selected records to a view" option is usefull for since this limit is not taken into account by rollups. Any ideas ?

51
Karlstens
S
Sara11Inspiring
posted in Formulas
How join two columns that have attachments?

Hi, I'm trying to merge two columns that have attachments. I have 1 column that includes the attachments where the user is the primary contact (1- Attachments- Purple Due in one week), and another one where he/she is the secondary contact(2- Attachments- Purple - Due in One Week). I need to merge those columns to create a "total attachments" column for users.The attachments are "white" because are just white pages of word (for now).I tried to use ArrayJoin, Concatenate and also &, but the result is the same.How can I solve this problem?? Thank you very much!  

22
S
Z
zeremKnown Participant
asked in Formulas
How to change the timezone correctly?

Hi dear friends.I would appreciate your help regarding time differences.The clock should be +2 as you can see in the picture but for some reason when I do the formula it takes me back 2 hours and I couldn't figure out how to use the timezone formula.I would appreciate your help on how to write the formula correctly so that it works.Thank you! 

990
Soly
A
Alba_Machado2Known Participant
asked in Formulas
Formula for converting decimals to fractions

I thought I was being clever but my formula did not work. Cells are blank. Why?SWITCH(  {Decimal},  "0.75",  "3/4",  "0.50",  "1/2")

180
A
Z
zeremKnown Participant
asked in Formulas
How to separate first names and last names in the most correct way?

Hey friendsI have a column of customer full names.I would like to separate this column into 2 separate columns - one for first names and the other for last names.I used the following formula (screenshot attached) to separate the first and last names, but there is a problem:If someone entered only their first name without a last name, the column remains empty and I don't understand why.I would be very happy for your help in this matter!Thank you 

1720
kuovonne
HaraldPalma
HaraldPalmaParticipating Frequently
asked in Formulas
Update due date based on IF formula

I am new here, so please forgive me if I ask a question that might have been asked before.I keep track of my software subscriptions with Airtable, some are monthly based and some are yearly. Therefore, I created a single selection field with two options (monthly, yearly) and a date field that shows when the subscriptions are due. Now I would like to automatically update the due date after it has passed under the condition of monthly or yearly. I had something in mind like: IF ({subscription} = "yearly", NOW(), DATEADD( *1, 'year')({subscription} = "monthly", NOW(), DATEADD( *1, 'month')I guess it's terribly wrong, since it never worked 😭 

50
HaraldPalma
Z
zeremKnown Participant
posted in Formulas
How can I separate the "date" into "date and "time" in separate columns?

Hey friendsI have a connection to Airtable that transfers every new lead to my table.I have a date column and an hour column, but I don't know how to separate it, so every time a new lead is updated, it is only updated with the date and the hour remains empty.How can I separate them so that both columns are updated with the relevant details?Thank you!

392
Z
M
Michael_TownsenInspiring
posted in Formulas
Dynamic form field set up

Hey, I have been looking for a while and I'm stuck.I am organizing group calls with 3-6 people per call.  After each call I want to each participator to fill out a form, giving feedback for each other participator on that call. The challenge I'm running into is how to collect feedback for each participator from each other participator. I went down the rabbit hole with prefilled forms, but I cannot connect the actually rating to each person. Maybe I've just hit the limit for airtable forms, but I'm curious if I'm missing anything obvious. Happy to explain more if this is unclear (however I was reluctant to share screenshots with personal detail)

192
TheTimeSavingCo
T
Tahir_JetterInspiring
posted in Formulas
Trying to Create an Automated Trigger and Not Sure If I'm On the Right Track

Hey All,I'm mocking something up and wondering if I've designed these tables incorrectly.I'm trying to create a connection between two tables (1. "Adjustment Funding Matrix" and 2. "All Adjustments Info," and specifically the "Policy Threshold" and "Final" fields both of which are in this latter table.)My goal is to make it so that based on the thresholds set by the "Adjustment Funding Matrix" table, depending on the financial amount in "Final," I'd like to make the "Policy Threshold" field in "All Adjustments Info" display the information in the corresponding "Policy" column from "Adjustment Funding Matrix."Is that possible the way I've set this up through a conditional relationship, a trigger, or something else? Thanks.

01
TheTimeSavingCo
T
TroelsNew Participant
posted in Formulas
List of e-mail adresses

I have a table containing info on contacts. My goal is a list of e-mail adresses from that table.The wanted e-mail adresses will typically be a subset from the main table. Here are among other things two fields saying something about every contact and these fields are the type "Multiple select".I would like to make a formular by which one can decide the wanted subset of e-mail adresses and then get them as a kind of list to be easily inserted in the to-field in a mailproram. The choise should be made with the mutiple select-fields.Does anybody have ideas on how to do that? Any hint will be deeply appreciated!

231
TheTimeSavingCo
T
Troy_HudsonNew Participant
posted in Formulas
DATEADD() pulling the count from a lookup field

Doing a simple add 2 or 3 days to a start date. DATEADD(Start,3,'days')   ---Note: the field names are purple but no longer add the {  } ??? I tried both ways. That adds just fine, however, when I change the 3 to a lookup field called Days formatted as integer it seems to subtract a day. or not any at all with different variations. DATEADD(Start,Days,'days')Can a number be pulled from a lookup field be used like this or do I need to cast the value somehow to a #Numnber format? When I use a field in the same table that is a number (integer) it works as expected. DATEADD(Start,number,'days')  I've tried adding in timezone same for all using GMT and America/New York UTC -5. same issues

842
T
V
vcsycNew Participant
posted in Formulas
Creating multiple records in same and different tables

Hi, I am stuck with a riddle ) I'm a newbie still don't know much. Any help is highly appreciated.I have several tables:1. Customers2. Products3. Currency4. Warehouse5. Accounts6. Operations8. Operations_Detail9. Acc I have two things that I need:1. when I create sales invoice in Operations, I need to create several records in operations with same AnalyticTag (column) as in created Invoice, but different Expense category (column).1.1 These records must be either profiled, or either from template - but this is different story I guess. 2. Also, when I create customer or Accounts, or add new currency - I need to create rows in ACC table. For example, I have Customer 1, Account 1 and Currency 1, 2, 3What I need is, when creating customer or account record, or adding new currency record, have a table in ACC like this:row 1 customer 1 currency 1row 2 customer 1 currency 2row 3 customer 1 currency 3row 4 account 1 currency 1row 5 account 1 currency 2row 6 account 1 currency 3&n

71
TheTimeSavingCo

Badge winners

Show all badges
Terms & Conditions