Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,228 Replies
5939 Topics
Scott_Gardner1
Scott_Gardner1Known Participant
posted in Formulas
IF FORMULA issue

Quick question, I need the Item Description to not be blank at all times (I need to see it in the primary field regardless), the way this is set up - the task needs to be selected to make the Item Description appear, thoughts? IF( AND( {ITEM DESCRIPTION}, {TASK TYPE}, NOT( {TASK NAME} ) ), {ITEM DESCRIPTION} & " - " & {TASK TYPE}, IF( AND( {ITEM DESCRIPTION}, {TASK TYPE}, {TASK NAME} ), {ITEM DESCRIPTION} & " - " & {TASK TYPE} & ": " & {TASK NAME} ) )

52
Ben_Young1
S
Sergio_XicaraNew Participant
asked in Formulas
Find the single value among many

I have a “Lookup” field that shows me many values ​​in the same field according to the tab relationship. For example: It is related to a field called orders, the lookup shows a field with the number of orders, and the next lookup shows the month in which they were made, but it shows me the same repeated many times, (July, July, July, August, July , August). What I need is a formula that breaks me down and shows me only the months (July, August).

41
Ben_Young1
K
Kyle_RollNew Participant
posted in Formulas
Include Saturday as a WORKDAY?

Hey all. I’m following this tutorial on auto calculating the end date based on the start date. I got it all working, but would like to include Saturday as a working day (so only exclude Sundays). I’m sure this is simple but keep getting errors in my formula. Here is the formula I’m working with which is based on the standard workday schedule: IF(AND({Start date},{Duration (days)}),WORKDAY({Start date},{Duration (days)})){Duration (days)} Thank you!

101
Zack_S
Nathan_Ramey
Nathan_RameyKnown Participant
asked in Formulas
Recurring formula for dates

Hello everybody! I hope everyone is doing well. I was trying to create a system where we have a due date for a item (in this case a bill), and then have a single select field with options to say how often we have to pay it. I then thought it would be great if I didn’t have to change the due date and that is done automatically. However the formula I created isn’t correctly adding the days based on what I have in the single select field. It defaults to the first if statement. My formula: IF(Frequency,“weekly”,DATETIME_FORMAT(DATEADD({Due Date},7,‘Day’),‘MM/DD/YYYY’),IF(Frequency,“bi-weekly”,DATETIME_FORMAT(DATEADD({Due Date},2,‘week’),‘MM/DD/YYYY’))) Note: This is not all the option but I figured it was a problem with the structure and not the options. Any help would be greatly appreciated. Thanks!

242
Nathan_Ramey
tiolan
tiolanNew Participant
posted in Formulas
Date difference between the values of the linked field

In the Client table I have the Meetings field, linked to Meeting table. A client can have multiple meetings. I need to know, if a client has any two meetings closer to each other, then 1 month. If the difference between any two dates is less then a month – formula must return “often”. Help me please! I can’t figure out…

112
tiolan
R
Rob_FraserNew Participant
posted in Formulas
Workday function is excluding Fridays and Including Sundays - Help

Hi all, Having an issue using the WORKDAY function. Here’s what I’m trying to do: I’m trying to create training schedule that automatically calculates the date of each training day, based on the start date and the day of training. EG If start date is Monday Aug 1, 2022 and we’re on training day 5, then the training date should be Aug 5, 2022. I’ve set the start date for each record to Aug 1, 2022 and the formula works beautifully until I get to training day 5, which should return as Aug 5, 2022. Instead Aug 7, 2022 is returned (a Sunday). This pattern continues, skipping Friday’s and including Sundays. Any advice?

11
ScottWorld
J
Jamie_RoodKnown Participant
posted in Formulas
HTML from Sendgrid pulling through for some fields but not others

Hi, I have designed an email in Sendgrid that I want to send to a filtered list of jobseekers in a table. This HTML references fields from a linked field and its associated lookup fields. E.g. I need it to display information from another table called ‘Open projects’. When I paste the HTML into the Sendgrid editor (Send as HTML is turned on) the first field from the linked field displays, but the other fields don’t, even though I have checked that the field is in the table and being referenced correctly. This is displayed in the screenshot above, whereby the ‘Name of opportunity’ is pulling through, but the other fields are not. Here is a link to the HTML file: Sendgrid Email Template HTML.txt - Google Drive Please if anybody can help me it would be greatly appreciated! Thank you!

122
ScottWorld
A
Antony_BearparkNew Participant
asked in Formulas
Datetime Difference

Struggling with the Date time difference not showing the correct status. The problem is the failing to return “Due Soon” instead of “Due Tomorrow”. Today, the date is 24 August, but projects marked with a 26 August deadline are showing as “Due Tomorrow” and I don’t know why… IF(OR({Status}="To Do", {Status}="WIP"), IF({Deadline}=BLANK(), "Not Due", IF({Deadline}=TODAY(), "Due Today", IF(DATETIME_DIFF( TODAY(), {Deadline}, 'days') <= 1, "Due Tomorrow", IF(DATETIME_DIFF( TODAY(), {Deadline}, 'days') > 1, "Due Soon", IF(IS_AFTER( TODAY(), {Deadline}), "Overdue"))))),"-")

51
TheTimeSavingCo
G
GABRYZZZNew Participant
posted in Formulas
Search airtable

I have a bot which, via the command /checkID followed by the ID to be searched, should see if it is inside the airtable and give a yes or no answer… Is this formula correct? {ID utente} = {{contains(1.message.text)}}

00
G
H
Huw_JonesNew Participant
asked in Formulas
Using a Button to create a URL

Hi, I’m pulling what little hair I have left out trying to search for an answer to my problem. Any advice would be very gratefully appreciated. I am trying to create a button that will create a custom url based on the following: https://random.company.co.uk/crsc/api/status/?dateOfBirth=<DD/MM/YYYY>&s urname=&hasAgreedTermsAndConditions=true&organisationName=&employeeSu rname=&employeeForename= The elements will be referenced from our airtable. Any clues as to where to start will be met with whoops of joy. Thanks, Huw

2312
H
P
Penny_BellInspiring
asked in Formulas
DATETIME_DIFF Formulas Help Needed! Negative Numbers

Why are all my DATETIME_DIFF formulas coming up as a negative number? My formula is: DATETIME_DIFF({Birthday}, TODAY(), ‘years’) and I am getting for example -29 instead of 29.

2210
ScottWorld
K
Kasey_SheeranNew Participant
posted in Formulas
Removing NaN from DATETIME-DIFF

I’ve found similar forums, but when I try to apply what I’m seeing to my table it’s not working so I’m missing something obvious :woozy_face: Looking to have this formula DATETIME_DIFF(Completion, KickOff, ‘days’) only run when the completion and kickoff fields are filled out to avoid the NaN result. Appreciate any help! Kasey

91
A
Cody_Winchester
Cody_WinchesterInspiring
asked in Formulas
Use FIND() to look for two strings in an array

I have table where I’m tracking attendance versus enrollment of a training workshop. I’m using a name field that also has a lookup field displaying whether someone goes by a formal name or an alias. I have another field that has an array of all attendees of the workshop an individual has enrolled in. I’ve had some success finding the enrollee’s name in the array of attendance to determine whether the enrollee is among those in the array. However, I’m running into an issue where, in some cases the enrollee, though listed as using an alias, may in fact have attended the workshop under their formal name. So, I need to use a formula that can search for and find either the alias or the formal name in the array, but it doesn’t seem to be working. Here’s what I have in the case of an enrollee who uses an alias who also attends the workshop using that alias: FIND( (IF({Alias (from Name)},{Alias (from Name)},Name)), {Workshop Session 000 Attendance}) I have tried including AND, but nothing is

1974
Cody_Winchester
S
Sarah_RichmanParticipating Frequently
posted in Formulas
Calculating School Grade Level From Grad Year

We’d like to have something that automatically populates what grade the student is in this year based on their expected grad year (the grad year doesn’t change, and the school grade level changes each year, so trying to avoid having to go back in and adjust the grade level each year…) any thoughts?

352
S
P
pier_bdtNew Participant
asked in Formulas
Create static date when checkbox is clicked

Hello, I would like to create a new static date when a checkbox is clicked. It should be [day the checkbox is clicked] + [X Days]. I’m actually using IF({Follow-up 1 sent?} = 1, (DATEADD(TODAY(),2,"days")),BLANK()) However the Today() function keeps updating the date to … today. I’d like the newly created date to be static and not be incremented every day by the TODAY() function. Thanks

516
P
S
Sara_OphoffKnown Participant
asked in Formulas
Using date time parse formula that includes a time range

Hello - I am trying to utilize a formula to view a date that is formatted like this Tue Aug 23, 2022 3pm - 3:30pm as 08/23/2022. (I do not need the time range in the final view) I was trying to do a formula like this: DATETIME_FORMAT(DATETIME_PARSE({Formula},“DDD MMM DD, YYYY”), “MMM-DD-YYYY”) but I am just getting an error in return Am I wildly off? Any help would be greatly appreciated! I am super new to formulas.

187
M
L
Lena_Osorio-GemParticipating Frequently
asked in Formulas
In desperate need of help with my formula

I am creating a table where our team enters LinkedIn social media analytics. I have only dabbled with formulas, so I went to my friend for help, and he gave me a good formula, but I think it needs tweaking. Here is my dilemma: First, I want the “Click-Through Rate” to display “Not Available” for future posts. For current posts (as well as past posts); I would like to display a number, even if it’s zero. If you notice in my screenshot, there were certain past posts with data entered, but it doesn’t display anything in the Click-Through Rate cells. :woman_shrugging:t5: Second, I would like to display the Click-Through Rate as a decimal that moves the decimal point. For example, instead of displaying 0.0198, I would like for it to display, 1.98% Here is the formula: IF({clicks} > 0, ROUND({Clicks}/{Impressions},2), if({Impressions} >0, ROUND({Clicks}/{Impressions},2), “Not Available”)) I appreciate any and all help.

1712
L
R
RaulGarciaNew Participant
posted in Formulas
Formula that adds up to one month and that the final result is not a weekend

Hi Everyone! We need to create a formula in airtable that from the column of the image (date of hire) where it indicates the day that the employee has been hired add 1 month and that the result is the first Tuesday of each month taking into account that each month has different days. And if it can be indicate us how to edit the formula to make that instead of Tuesday falls on Wednesday for example. I don’t know if I make myself clear. Thank you so much!! P.D: Here is the formula I am trying and the column which I need to add one month.

42
R
T
ToriAnn_PerkeyNew Participant
posted in Formulas
Workday Formulas - How do they calculate weekends?

I’m new to Airtable and just getting a handle on formulas. I’ve done quite a bit of searching, but I can’t find a current answer to this. I’m trying to set up a simple tentative timeline of due dates. The project is done multiple times and there are a standard number of days between each step. I’ve created a simple table where the start of the project is entered manually and then each additional step uses this formula to calculate the next date: For example: WORKDAY({Forms Sent}, 2) With the {Forms Sent} being the previous step and the number being the number of days the current step takes. However, I can’t figure out if the workday starts on Sunday or Monday. (Based on testing, it seems to start on Sunday.) Our workday is M-F, so if it is set to start on Sunday, is there a way to adjust it? Thanks!

191
TheTimeSavingCo
J
Jason_FKnown Participant
posted in Formulas
Date Formula Result I Need to Copy to LinkedField

I have a formula that takes a datefield and formats it the same way as my Primary Field, in my link table called Room Moved for Copy,. I have automation I want to run that will watch the Date Field for any time it updates and then I want to take the new result in Room Moved for Copy and paste it into my Time Slots Linked Field. I am getting an error. I suspect it is because the result is not a sting but not sure. DATETIME_FORMAT({Meeting Start}, “MM/DD/YYYY HH:mm”) Any help getting this to work would be amazing!

31
TheTimeSavingCo
K
Kevin_BowerNew Participant
asked in Formulas
Need to mark a record based on a time range

I have a table where I need to mark when flights take off between 22:00 - 07:00. I would like an If statement where it marks the record with an X when this is true. I have date fields for the departure time but I just cant quite find how to have a general standing marker based on times rather that a specific time AND date.

62
ScottWorld
D
Dismal_ReindeerNew Participant
asked in Formulas
Formula to search a roll up array and = true or false

Maybe I’m just not looking in the right places but I cannot figure this out. I have a rollup field which is outputting ARRAYUNIQUE(values) so I end up with “Product1, Product2, Product3” I now want to have three other formula fields which are: “Has Product 1” “Has Product 2” “Has Product 3” that will equal “True”, “False” if the the product is found in the rollup string. How can I go about this?

872
D
Leah_Relish
Leah_RelishKnown Participant
posted in Formulas
IF Formula for naming a field

Hello Community! I am struggling with writing a formula for naming the main field in a table. I would like the formula to name the field {Company} if the field {Company} is not blank and name the field {Last Name} - {First Name} if the {Company} field is blank. I just can’t seem to get it right. Can anyone help? Thanks!

116
Leah_Relish
A
Adam_SharrowNew Participant
posted in Formulas
If a property has a value return Yes to a separate property

I would like to create a property on my table that returns a value of “Yes” if another property on the table has any value in the cell Think it’s a fairly easy IF statement but am struggling to complete. Property Names Added to Website Website Link IF(Website Link is known) THEN add text to Added to Website = “Yes”) Thanks!

31
Kamille_Parks11
Nedra_Hines
Nedra_HinesInspiring
posted in Formulas
Get rid of #ERROR!

Hey everyone! Yes, I know this has been answered before and I’ve read several posts. I’m not an airtable expert and I do want to get rid of the #ERROR! result when {Monthly Rental Fee Renewal Date} field is blank: IF(IS_BEFORE({DeStaging Date},{Monthly Rental Fee Renewal Date}),“No renewal charge”,CONCATENATE(DATETIME_FORMAT({Monthly Rental Fee Renewal Date},‘MM-DD-YY’)," | “,{Client Name},” | ",“Rental Fee Renewal Date”))

73
Nedra_Hines

Badge winners

  • First Reply
    GegHhas earned the badge First Reply
  • Welcome
    youssefhas earned the badge Welcome
  • First Topic
    _Chris_has earned the badge First Topic
  • First Topic
    GegHhas earned the badge First Topic
  • Welcome
    blessing333has earned the badge Welcome
Show all badges
Terms & Conditions