Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,225 Replies
5939 Topics
A
Audrey_SchmittNew Participant
posted in Formulas
Working days and employee leaves

Hi everyone, I’m currently working on a base for my HR using KasPer lite HT base (thanks for creating it). I’ve managed (ish) to calculate the working days minus public holidays for a specified month. That said, I want now to calculate the number of working days - public holidays - employee leave for a specific month. For example, in June, you have 21 working days, employee 1 took 4 days off in June, so employee 1 worked 17 days… But I can’t figure out how to do it. Employee leave are registered in a simple absence table with the following fields : Employee Start date End Date Leave year start Leave year end … Sorry if it’s not cristal clear, I’m a beginner with Airtable, and I do not want to go back to Excel… Thanks a lot in advance for your help.

746
TheTimeSavingCo
G
Grace_HuiNew Participant
asked in Formulas
What is the Excel DateValue Equivalent to in AirTable?

Hi - I’m looking to do an if and statement with two dates. If between X and Y date, then label it as “A” else “B”. But I am having trouble with finding the date value equivalent in airtable. In Excel I would do: IF(AND(CELL1>=DATEVALUE(“04/01/2020”), CELL2<=DATEVALUE(“03/31/2021”)),“FY21”, " ") But I can’t find the equivilant of that in airtable. I currently have the following where timestamp = my field IF(AND(Timestamp>=DATETIME_PARSE('04/01/2020'), Timestamp<=DATETIME_PARSE('03/31/2021')),"FY21", "")

102
G
P
Project_NP_LSKnown Participant
posted in Formulas
Formula - sum of fields

I have 10 fields in a table, and some contain numbers and the others either a letter or a symbol, in the same table I would like to have another field that sums up the values of the numbers only in that record. Is there a simple way/formula to do this? Does Airtable have an equivalent formula to excel’s SUMPRODUCT & ISNUMBER? Best Ladi

185
P
R
Robbi_BehrNew Participant
asked in Formulas
If Due Date is Today, Enter X in Field

I’m trying to set up an automation that will send an email reminder at a later date to complete a task. I have set up one column with “Due Date” another as “Today” and a third as “Send”. I have set up the following formula that when “Due Date” = “Today” it should put an “X” in the “Send” column. My automation is set to send the email when the “Send” field is updated with an X in it. Here is the formula: IF({Due Date} = {Today},“X”) It did not work. I also tried: IF(IS_SAME({Due Date},TODAY()),“X”) That didn’t work either. Just to test it, I tried: IF(IS_SAME({Due Date},TODAY()),“X”,“Y”) and then all the columns filled with Y, even the one where the Due Date matched Today. ???

163
R
K
Kel_AspKnown Participant
asked in Formulas
Conditional formula - If/Or/And - Not sure which

Ok this seems pretty simple, but I just can’t get my head around it. I am new to formulas, so get the basics, but want to move beyond. Fields are - Member, Age, Clearance, Expiry, Valid, Current Member, Needs attention. Basically, if the Valid field is ‘not valid’ and the current member is ‘yes’ then the needs attention should be ‘yes’ However, if the member is not yet 18 then clearance etc is not required. So far I have IF( Age<18,“Not Required”, IF(AND(Age>17, Clearance,Expiry), “Valid”, “Not Valid”)) But I would like it to only be valid if the Expiry is after ‘today’. I really don’t know if I am explaining it well. I have created an example table here to help explain.

82
K
O
Omar_ShraimNew Participant
posted in Formulas
Formula or Automation to change the value of a Single Select field

I have the following formula field that works perfectly to set the Expiry Status. The problem with having a text Expiry Status is that I cannot use it for KANBAN views (and also it does not show in cool colors!). If I had a single select field for Expiry Status, would there be a way to have a formula that can change the value of the single select field instead of the formula that outputs the status as text? If not with a formula, is there a way for Automation to change the value of a single select field based on the logic I am using in the formula? The formula for the Expiry Status field: IF({Months for expiry}=BLANK() ,BLANK() ,IF({Months for expiry}>24 ,“More than 2 years valid” ,IF({Months for expiry}>12 ,“Less than 2 years valid” ,IF({Months for expiry}>6 ,“Less than 1 year valid” ,IF({Months for expiry}>3 ,“Less than 6 months valid” ,IF({Months for expiry}<0 ,“Expired” ,“Less than 3 months valid” ) ) ) ) ) )

242
O
K
Kristin_TurnerNew Participant
posted in Formulas
If "QTY Used in Part" Equals 4, 550, or 1000 Then "correct" if not then "incorrect"

Not sure how to properly write this out in a formula. I’ve got it working with this one formula: IF({QTY Used in Part}=1000,“ :white_check_mark: Good :white_check_mark: ”,“ :red_circle: Incorrect :red_circle: ”) I’ve tried just using a bunch of IF statements, I’ve tried to make AND/OR work to no avail (probably not doing it right), I’ve read about Switch but am unsure of how to make it work for my needs - I have no idea where to go from here. Please help.

03
ScottWorld
M
Manish_PatellParticipating Frequently
posted in Formulas
Is it possible to GET data from external API to Airtable?

how can we get data from API in Airtable? below is the sample API curl -X GET "https://apiv2.shiprocket.in/v1/external/courier/track/shipment/1559017361980" -H "accept: application/json" is it possible ?

935
JonathanBowen
C
clay272New Participant
posted in Formulas
Filter by formula based on date and hour

I’m using this filterByFormula which works perfectly fine based on date, but I want it to work based on hour as well. Just keep it the same way it is with hour functionality. filterByFormula=IS_AFTER(LAST_MODIFIED_TIME(), DATETIME_PARSE("6/18/2022", 'locale')) So for example for todays date which is 6/18/2022 be able to do 6/18/2022 20:00 What is the proper format to do it?

41
TheTimeSavingCo
A
Ambroise_DhenaiInspiring
posted in Formulas
Check if date is before/after any of a list of dates

I have the following value 2022-06-01, 2022-12-05 and I need a formula that checks whether: 2022-06-01 is after TODAY() or 2022-12-05 is after TODAY() How can I do that? Doing that with a single date (e.g: 2022-06-01) is easy, but how can I handle that for a list? Note: 2022-06-01, 2022-12-05 is a rollup value of a one-to-many relationship, I don’t think I can use the relationship to perform any kind of calculation there, though. (I don’t see how that would help)

494
Alexey_Gusev
J
Joachim_BrindeaInspiring
posted in Formulas
MAX(values) in rollup not working when nested in IF function

Hello everyone, :wave: I’m having some odd behaviour I can’t figure out. I have a formula rolling up invoices linked to a case. IF(Statut = "Clos et facturé",MAX(values),"Dossier ouvert") It does not work. The cell shows up empty when it should show the last invoice date. But if I try: MAX(values) It works. And if I try: IF(Statut = "Clos et facturé","test","Dossier ouvert") It works too. I’m puzzled as to why my first formula does not work. Thank you for your help, Joachim

01
Kamille_Parks11
A
Alex_VinogradovNew Participant
posted in Formulas
Get record_id() of a linked table record?

I have two tables: Table A (i can modify it) Todo text (just a text field) Related Issue Title (linked table field) Related Issue Link (the field I’m questioning) Table B (can not create new fields) Title Description Status I know that there is a function RECORD_ID() which returns the current row’s record id. I also know that the link to the record looks like that: 'airtable.com/appxxxxxxxxx/tblxxxxxxxxx/viwxxxxxxxx/recxxxxxxxx' So I need to generate URLs to the Referenced Issue in a separate field in Table A I also can not enable the scripting app for this field because it’s disabled for security reasons by our admin :slightly_smiling_face: I expect the answer to be “it can not be done” but asking just in case…

1371
kuovonne
B
Brite_AdminInspiring
posted in Formulas
Rollup array formula with entries in reverse date order

Please look at my screenshot. The entries come in with the most recent at the bottom (date order). Is there any way to reverse the order so the most recent entry appears at the top? Thanks for any help!

211
kuovonne
T
Tim1New Participant
posted in Formulas
Ignoring text when calculating cells that can contain numbers or text

Hi Community - I have a formula that performs a simple calculation by summing a series of cells. The challenge comes in that some of these cells contains text, rather than numbers. I would like the formula to ignore the text so that a total can be calculated. Currently, a ‘NaN’ is being returned. I attach an image of the cells that I am trying to calculate and the simple formula is: {1-Jul}+{4-Jul}+{4-Jul}+{5-Jul}+{6-Jul}+{7-Jul}+{8-Jul}+{11-Jul}+{12-Jul}+{13-Jul}+{14-Jul}+{15-Jul}+{18-Jul}+{19-Jul}+{20-Jul}+{21-Jul}+{22-Jul}+{25-Jul}+{26-Jul}+{27-Jul}+{28-Jul} Thanks in advance

202
T
S
Smyth_CountyParticipating Frequently
posted in Formulas
Nested Formula Issues

Not sure this is possible but I can’t seem to get this to work when I add the second part. I have a formula that works this way 'If Base Rate = 0 and IF UM= “Pounds” then Weight / 2000 * Price, IF Base =0 and IF UM= “Kilograms” then Weight / 2200 * Price, IF Base Rate = 0 and IF UM = “Each” then “Price” I need to add this: 'IF Base Rate Not Equal to 0, and IF UM=“Pounds” then Set Weight/2000 * Price, IF Base Rate not eqaul to 0 and IF UM =“Kilograms” then Set Weight / 2200 * Price, If Base Rate not equal to 0 and If UM =“Each” then “Price” My Formula: IF({Base Rate } = 0,IF({UM} = “POUNDS”, ({Weight}/2000 * {Price}), IF({Base Rate UM} = “KILOGRAMS”, ({Weight}/2200 * {Price) , IF({UM} = “EACH”, {Price}, IF({Base Rate} !=0, IF({UM}= “POUNDS”, ({Set Weight}/2000 * {Price})… It computes the first part when the base rate is 0, but when the base rate is not 0, that part of the formula doesnt compute. Any insight would be appreciated.

01
Ben_Young1
matt_stewart1
matt_stewart1Inspiring
posted in Formulas
Dates formatting to middle of cell now?

Date fields and formula fields with dates are now formatting to center left of cell? There is no ability to adjust text placement that I can see so it now sits out of place with other cells data visually. its also annoying that some cells format top right, and others top left. Can we have formatting options for columns?

13
ScottWorld
J
Joachim_BrindeaInspiring
posted in Formulas
Fixing regex for lastname that contain two words

Hello everyone, I’m trying to separate firstname and lastname using formulas I found online. My issue is that some french name are like: John De Doe The first word is always the firstname, the rest is the lastname. I have this formula for firstname: IF({Nom complet}, REGEX_EXTRACT({Nom complet}, ".* ")) And I have this formula for lastname: IF({Nom complet}, REGEX_EXTRACT({Nom complet}, "\\s(.*)")) It outputs “John De” as firstname instead of “John”. and “De Doe” as lastname which is what I want. I need a formula to get first word (preferably using regex) that works with special characters. Thank you for your help, Joachim

00
J
M
Michael_Wood1New Participant
posted in Formulas
Calculate difference in time from a 'Created time' field across two rows

Hello, all Airtablers, I’ve created a simple ‘Clocking In/Out’ Airtable for a colleague to track staff signing in and out. It uses a simple form where the user selects their name from a single select field and then selects either ‘In’ or ‘Out’ from another single select field and then submits the form. In the table itself, each member of staff should have an ‘In’ AND ‘Out’ record for each day with the automatically generated date and time. Now is there a way to calculate the difference on the ‘In’ and ‘Out’ times for that staff member for that day as the values are on different records?

52
M
L
LaToya_WilliamsNew Participant
posted in Formulas
Help with Date Range Formula...if Due Date field is between 1/1/2022 and 3/31/2022 then "Q1 2022" and so on

Hello hello Airtable Fam. I have a question about formulas. So I have a “Due Date” field that I want to use as my constant to determine what Quarter of the year it’s in over 3 years. So I want a formula that will calculate: if the due date is between 1/1/2022 and 3/31/2022 that I’ll get a value of Q1 2022 OR if the due date is between 4/1/2022 and 6/30/2022 that I’ll get a value of Q2 2022 OR if the due date is between 7/1/2022 and 9/30/2022 that I’ll get a value of Q3 2022 OR if the due date is between 10/1/2022 and 12/31/2022 that I’ll get a value of Q4 2022 OR if the due date is between 1/1/2023 and 3/31/2023 that I’ll get a value of Q1 2023 OR if the due date is between 4/1/2023 and 6/30/2023 that I’ll get a value of Q2 2023 OR if the due date is between 7/1/2023 and 9/30/2023 that I’ll get a value of Q3 2023 OR if the due date is between 10/1/2023 and 12/31/2023 that I’ll get a value of Q4 2023 OR if the due date is between 1/1/2024 and 3/31/2024 that I’ll get a value of Q1 2024

51
kuovonne
F
Facilities_DepaNew Participant
asked in Formulas
Formular to create due time status based on Monthly, Quarterly & Annually

Hello, I’m creating a maintenance schedule but I’m having difficulties create a formula that shows me the “due status” based on how many days ago an item was serviced. Individual item have different frequency of service: Monthly, Quarterly, Annually, and so on. I have a single select field that dictate the frequency for each type or maintenance. I was able to create a formula to calculate cost using a base cost * how often we perform it, but I’m having trouble applying it to idea I’m trying to perform. What I’m trying to do is IF an item was serviced today, and its frequency is MONTHLY, It will be “On Time” for 30 days, by the 25th to 29th day it will “Be Due Soon”, On the 30th day it will be “Due today”, and on the 31st or passing the 31st day will be “Overdue”. This is what I kind of have but it’s probably wrong.

202
F
A
AnxiousInspiring
asked in Formulas
Format the text in a rollup field (Concatenate?)

Is there a way to format the items in the rollup field to look like this (including brackets on either side): [“Choice1”,“Choice2”,“Choice3”,“Choice4”,“Choice5”] I have this formula but it does not work in a rollup field: '["' & SUBSTITUTE( Choices, ', ', '","' ) & '"]' Then I found this sample code: IF( ARRAYJOIN(values), "- " & ARRAYJOIN(values, "\n- ") ) But I can’t figure out how to combine them without an error. IF( ARRAYUNIQUE(values), '["' & ARRAYUNIQUE(values, ', ', '",") & '"]' ) All these “,”"’’ are making me go cross eyed. Please help me! :rofl:

610
A
A
AnxiousInspiring
asked in Formulas
Separate multiselect options into separate fields

I have a multi-select field that has a large number of choices. I have decided it would be better to separate them into different categories (fields). Is there a way to do this? I have a master list of choices in a separate table (if that helps). These choices have a name and a description and a category. Is it possible to do something like this: Category 1 Field Look at the main select fields and find all choices. Look at the master choice list and find the choice name that matches the category. Place the choice that matches the field category into the field. I am on a free plan and have no access to paid tools. Example base for explanation purposes: Airtable Example Base - Airtable Explore the "Example Base" base on Airtable.

1063
A
J
Joan_BornNew Participant
posted in Formulas
Assign a due date of Thursday for all items due in a certain week

Hi - I’m trying to set a due date that is two weeks out from a publish date on the nearest Thursday. Essentially, everything that is set to be published in week 25, should be due the Thursday of week 23. Does anyone have any hot tips on how to formulate this one?

31
M
J
Jeffrey_BrianKnown Participant
posted in Formulas
Formula that will rollup but exclude certain single select records

Hey everyone, I was wondering if there is a way to use a formula while rollingup information from a linked table that can exclude certain records that are attached to specific single selects in the table that has the information that is being rolled up? I am trying to extract insights from my projects table into another reports like table, but am running into an issue where projects that I am currently working on that aren’t fully finished with the total time it took for the project/ how much I have made from the project are figured out which are interfering with me creating acurate insight amounts. Any help is greatly appreciated, thank you!

412
J
D
Dave_k16New Participant
asked in Formulas
Better way to count no. of checkboxes

Hello, I’m relatively new to Airtable, so I have a question if something I’m doing can be improved: I have a base where I am counting the number of checked boxes (so far so good) However, I will create a large number of bases where the task name varies and won’t be a simple Task1 etc. Is there a way to count the no. of checked boxes without referencing the Task1, but something along the lines of referencing the cells (like in excel) This way I can just duplicate the base and change the “task” name for each base as needed, without needing to change the formula all the time. But the formula would work regardless of what the task name is. Thanks!

1034
Kamille_Parks11

Badge winners

Show all badges
Terms & Conditions