Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,945 Topics
  • 18,248 Replies
5945 Topics
D
Dani_OliverKnown Participant
asked in Formulas
Formula for assigning value to date ranges

Hey there. I’m trying to create a field that populates the fiscal year that a project is published in, as it’s different from the actual year. So if the date of the project is between December 1, 2003 and November 30, 2004, it’s FY04. And so on for the following years ad infinitum. Any help would be appreciated; I’m having trouble with date formulas and I trawled through posts but couldn’t find a solution that worked for me. Thanks in advance.

145
Z
C
Cameron_MasonNew Participant
asked in Formulas
Future Date Formula

I’m trying to use a formula field to calculate a date for benefits eligibility like health insurance. For example, if an employee starts on August 6th, 2020 we require them to wait until the first of the month following their first 90 days of employment until they are eligible. With the DATEADD formula, I can add 90 days, but I’m not sure how to take the result from 90 days to the first of the following month. With the example above the result I’m looking for would be December 1st, 2020.

272
C
A
Ashley_SpeyerNew Participant
asked in Formulas
Help with a Nested If Statement

Hi - I am trying to write out a long nested IF statement and am having issues! Can anyone help with this formula? IF(AND({What category survey did you work on?}=“Category 1”,{Did you also conduct Qual Coding?}=“No”),{Cat 1 Survey Rate (LC) (from Countries | 60dB base)}, IF(AND({What category survey did you work on?}=“Category 1”,{Did you also conduct Qual Coding?}=“Yes”),{Cat 1 QC Survey Rate (LC) (from Countries | 60dB base)}, IF(AND({What category survey did you work on?}=“Category 2”,{Did you also conduct Qual Coding?}=“No”), {Cat 2 Survey Rate (LC) (from Countries | 60dB base)}, IF(AND( {What category survey did you work on?}=“Category 2”,{Did you also conduct Qual Coding?}=“Yes”),{Cat 2 QC Survey Rate (LC) (from Countries | 60dB base)}, IF(AND({What category survey did you work on?}=“Category 3”,{Did you also conduct Qual Coding?}=“Yes”),{Cat 3 QC Survey Rate (LC) (from Countries | 60dB base)}, IF(AND({What category survey did you work on?}=“Category 3”,{Did you also conduct Qual

44
A
A
Ashley_ConwayNew Participant
asked in Formulas
If Invoice is past due, display number of days past due

I’m getting stuck on this formula, please help! I have the following fields: Status (single select) Due Date (date) I would like to have a new field “days past due” that shows the number of days past the due date IF the status is “past due”. Otherwise, leave the field blank. I’m probably close (??) , but I tried the following two methods and am getting an error. IF({{Due Date} >= TODAY()}, (DATETIME_DIFF()({Due Date},TODAY,‘day’)), " ") IF({Status} = “Past Due”), ((DATETIME_DIFF()({Due Date},TODAY,‘day’)), " ")

485
J
D
Debbie_KagyNew Participant
posted in Formulas
Simple Division of Two Fields

I am trying to divide the total of two fields. The first is currency, the second is a number. Wanting currency as a result; eg total weekly pay / total hours worked = average hourly pay. Here’s what I’ve tried and both have come back as INFINITY: SUM({Total Paid})/SUM(Hours) SUM({Total Paid}/{Hours}) This gets an error: SUM({Total Paid})/SUM{(Hours)}

2016
Justin_Barrett
T
Tim_BruceNew Participant
posted in Formulas
Help returning lookup field value in URL parameter

To minimise duplication of data across my table I’ve converted a large number of Single Line Text fields to Lookup fields linking from a central sheet. Unfortunately the main use case for this data was access via URL parameter filterbyformula method from the branched sheets (this is important as there are some unique values on these sheets too). I was previously using: filterByFormula=Find("Tuesday", Day) The associated values in the to Name, Description, Price fields were being returned to populate components in an interface design tool I use called Framer. Unfortunately I’ve just learned that because they are now Lookup fields (eg calculated fields) it’s not that simple. The values are no longer populating. Testing shows its the Lookup fields causing the issue and a bit of research revealed that I may be able to to use an array function to bridge the gap. I’ve tried versions of the following formula but to no avail: filterByFormula=Find("Tuesday", ARRAYJOIN(Day)) Do I need to refere

193
Justin_Barrett
N
N_HNew Participant
posted in Formulas
How to Average a range of records

Hello! I am somewhat new to airtable so still trying to figure out some basics. What I am trying to do here is take the average values of each categories per seven days and average them. Is this possible? I.e. I would like to take the measurement of weight in lbs from days 1-7 and average that giving the result in another column, then for 8-14, 15-21, etc . . . I want to do this also for the columns as well. So far I cannot find a way to do this. Bonus: If it’s possible to specify this by the Date field every seven days starting on a certain day even better!

532
JonathanBowen
K
Kristin_CoxNew Participant
asked in Formulas
How to add a Third Condition to an IF Formula so that if the value is negative it will return 0

This is the current formula I am using; IF({3P Impressions}>0,{Booked Revenue}-({Booked Revenue}{3P Pacing}),{Booked Revenue}-({Booked Revenue}{1P Pacing})) But when the value returned is negative, I’d like to display “0.” The format of the field is currency.

122
K
E
Eva-Maria_SigurParticipating Frequently
posted in Formulas
EAN-13 barcode generator

I am new to airtable and I am using airtable as our Products database, and when we make a new product it needs to get a EAN-13 barcode. We get the first 9 numbers from the government, then we add the next 3 and the last one is calculated. Field 1 = 9 first diget Field 2 = auto number Field 3 = adding “0” so the cell has 3 number in it Field 4 = Field 1 & Field 3 Field 5 = calculated last number Field 6 = Field 4 & Field 5 The formula is: CEILING((VALUE(MID({EAN+rad},1,1))*1)+(VALUE(MID({EAN+rad},2,1))*3)+(VALUE(MID({EAN+rad},3,1))*1)+(VALUE(MID({EAN+rad},4,1))*3)+(VALUE(MID({EAN+rad},5,1))*1)+(VALUE(MID({EAN+rad},6,1))*3)+(VALUE(MID({EAN+rad},7,1))*1)+(VALUE(MID({EAN+rad},8,1))*3)+(VALUE(MID({EAN+rad},9,1))*1)+(VALUE(MID({EAN+rad},10,1))*3)+(VALUE(MID({EAN+rad},11,1))*1)+(VALUE(MID({EAN+rad},12,1))*3),10)-((VALUE(MID({EAN+rad},1,1))*1)+(VALUE(MID({EAN+rad},2,1))*3)+(VALUE(MID({EAN+rad},3,1))*1)+(VALUE(MID({EAN+rad},4,1))*3)+(VALUE(MID({EAN+rad},5,1))*1)+(VALUE(MID({EAN+rad},6,

182
E
A
Aernout_de_BeauParticipating Frequently
posted in Formulas
Data type of rollup unknown

Hi all, Who can help me out with converting a rollup result to a specific data type? I use a rollup field with ARRAYUNIQUE(values) in which the values are dates. Normally, this will be one single date (since I am checking if all values in the array are the same). The field of origin is of the date/time type (which I have enforced by a formula and Airtable recognizes it as a date). However, the date as displayed in the rollup field can not be manipulated by any formula in another field. It appears as if the data type of the rollup field (which is an array, of course) can not be determined, which makes it impossible to convert. Things I have tried that did not work: ARRAYJOIN(ARRAYUNIQUE(values), "; ") to convert the array into a single string. DATETIME_FORMAT(ARRAYUNIQUE(values), ‘D/M/YYYY’) as the rollup formula to convert the array into a date (or even an array of dates) Any ideas?

01
Justin_Barrett
J
Julian_AguilarNew Participant
asked in Formulas
I need help with the Formula Field (If And, If Not)

Hi there! I’m trying to build out a formula that allows me to use checkboxes to update an application status column. As of now, this is my current formula: IF( AND({Reviewed}, {Accepted}), ‘Accepted’, IF( {Accepted}, ‘Error!’, IF( NOT({Reviewed}), ‘Pending Review’, ‘Rejected’ ) ) ) My hope is to have a three-column system where if you check {Reviewed} it displays the formula column as ‘Reviewed’, {Reviewed} and {Accepted} as ‘Accepted’, {Reviewed} and {Rejected} as ‘Rejected’, and {Reviewed} and {Accepted} and {Rejected} as ‘Error!’. Please let me know how to go about this. Major thanks!

112
J
J
Janae_YoungNew Participant
posted in Formulas
Transfer rows from one sheet to another sheet

I was wondering if there was a way to transfer one row of information from one spreadsheet to another. For example, we have a spreadsheet titled Fosters that has a list of our fosters and it links to our Cat spreadsheet so we can see who is fostering what cat. If a foster then decides to adopt that cat, is there a function that would allow us to check a box or something that would move the person and their information from the Foster spreadsheet to the Adopter spreadsheet?

856
M
J
Jennifer_GreybeNew Participant
asked in Formulas
Need help with a formula to calculate end time based on start time and duration!

Hi Airtable community! I am planning a conference that has multiple sessions per day over multiple days, and as we tweak the schedule, I don’t want to have to manually compute the session start/end times. The duration of each session is pretty set in stone. I need a formula that will calculate the END TIME of an event based on the START TIME and DURATION of that event. For example, I have a session starting on 10/19/20 at 12:00pm. It lasts 20 minutes. It ends on 10/19/20 at 12:20pm. But that might change, and I’d like to be able to update either the start time or duration and not have to manually update the end time. Any assistance here would be much appreciated! Thank you, Jennifer

1044
J
B
Brite_AdminInspiring
posted in Formulas
Single list by date from two different fields

I have a field with birthdays and another field with wedding anniversaries. I have both feeding a calendar view but how can I create a single list in grid view sorted by the date that combines both fields. 1/1/2020 Birthday 1/15/2020 Anniversary 2/1/2020, Birthday etc.

40
B
E
EbbaInspiring
asked in Formulas
Formula for auto-updating single select fields

Hi everyone! I was wondering if someone may be able to assist. I am quite new to Airtable and just getting my head around formulas. I am looking to create a formula to auto-update the STATUS of a client depending on how many jobs we have done for them. I have created a STATUS column which contains the three single-line field options of HOT WARM and COLD. I have another column which records the AMOUNT OF JOBS we have done as a number. COLD would = 0 visits, WARM = 1-4 visits, HOT = 5+ visits. Is there is a formula I could use for this? Thank you so much in advance :slightly_smiling_face:

143
Nathalie_Collin
B
Brite_AdminInspiring
posted in Formulas
Concatenate 2 collaborator fields

I have two fields that use the Collaborator field in a table (Lead & Support). In order to do some filtering, I need to have a field that concatenates both collaborator fields but Airtable doesn’t want to let me. Any workaround? Thanks!

276
JonathanBowen
C
Camila_Cortes_CParticipating Frequently
posted in Formulas
Retrieve last prescription for a given Patient

Hi guys! I work at a public mental health service and we use airtable to coordinate our work. I have a patients table linked to a visits table, which in turn is linked to a prescription table, which is finally linked to a drug table. I want to be able to retrieve the last drug a patient has received in the visits table. How could I go about doing that? Thanks in advance!

92
B
B
Brett_LeeNew Participant
asked in Formulas
Can I do a formula for one card?

With my business, I have WAY too many deals that come across that are unique and individual from others. I am in DESPERATE need to find a way to create a formula, (or simply type in a number for that matter) and NOT have it affect ANY OTHER record. Just change the formula on that one particular record instead of every single one. For example. Right now my formula to figure out commissions looks like this: IF({PPW}=“4.35”,500, IF({PPW}=“4.25”,450,IF({PPW}=“4.15”,400, IF({PPW}=“4.05”, 350, IF({PPW}=“3.95”,300, IF({PPW}=“3.85”, 250,0)) ))))*kW This morning we sold a deal at $4.79. So none of the formula triggers did anything and they didn’t calculate. However, I want to pay him $600 at that price. So if I typed in $600, it would change that cell for every single record to $600 and completely ruin all our accounting. This happens multiple times a week, and I’m fear I may have to go back to an excel spreadsheet where I can do individual cells like that. I REALLY don’t want to go back to exc

1110
Justin_Barrett
C
Cathy_AndersonInspiring
posted in Formulas
IF comparing 3 fields

IF({Gift Credit Count}, IF({Gift Credit Count}<{Soft Credit Count}, "Softs", IF({Gift Credit Count}<{Appeal Credit Count}, "Appeals", "Gifts")))) 3 fields I need to compare. Gift, Soft, and Appeal. I am trying to create a formula comparing Gift to both Soft and Appeal. If Gift is larger, than “Gift” But if the other is larger, than “Soft” or “Appeal” I am totally lost. Because the formula above work when “Gift” has a number above 0 but not when the gift number is 0 and if @Justin_Barrett answers this one too, I am buying you a coffee!

63
kuovonne
D
Dre_GonzalezNew Participant
posted in Formulas
Cost +margin = price

We have a cost for an item {House Cost} We have a margin {Margin} We want {Customer Price} to have this field update as below. Example House Cost $1.00 Margin 40% Customer Price $1.40

21
G
A
AnnieKNew Participant
asked in Formulas
Disinclude empty values in formula

Hello, I’m trying to calculate an hourly rate using a duration field and a currency field. I have a formula field that calculates $/hr: ({Earnings}*3600)/{Hours Worked} However, I have a few records where I don’t have the number of hours worked, just the earnings amount. The formula calculates this as “infinity,” which messes with my sum of the entire column. Is there a way to tell this formula to leave the cell blank if there’s no hours worked?

41
ScottWorld
B
Brilliant_AdminParticipating Frequently
posted in Formulas
Referencing a rollup field in the table ID causes circular reference

I have a rollup field that gives us a quantity on hand of specific products - referencing an inventory (orders) table. I want to reference that field in the first column - the ID of our product table, so when it is referenced via linked tables, we can see the quantity on hand easily. However, this causes a circular reference error. Is there a way to do this?

40
B
B
Brent_HayesNew Participant
asked in Formulas
Convert Mmm-YY single line text field to its date field equivalent

what is the simplest way to convert a text field which is the three letter month '-'two letter year (Dec-02, May-18, etc) to a usable date field of the same format. The day of the month isn’t important, could be set to 01.

255
J
N
Nat_WisemanNew Participant
asked in Formulas
Calculate record number in particular view

I would like to capture the record number that appears on the LHS of each record in a particular view in a field (when you hover over the record this number turns into a check box). I want to print labels for each record, and I want to have a field which shows what number the label is out of the total records in that view (i.e. “3 of 10”). Or is there another way to calculate the order of records using formulas? Any help would be much appreciated!

202
N
P
Peter_SoumasNew Participant
posted in Formulas
Rounding up a number to nearest 50

I am trying to create a formula to roundup a number to the next highest multiple of 50. Can help much appreciated.

331
ScottWorld

Badge winners

Show all badges
Terms & Conditions