Formulas

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!

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!

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…

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?

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!

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"))))),"-")

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)}}

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

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.

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

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

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?

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

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.

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.

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.

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!

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!

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.

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?

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!

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!

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”))

Hello, I need to validate zip codes in applications against a list of zip codes within certain city limits. For example, if an applicant submits a zip code and it’s not listed in my other table containing a list of eligible zip codes, there would be a column flagging me something like need verification or ineligible. Is there a formula or some way to do this? Thank you!

Example I have a field with the following data: {“blog”:“abc dot com”, “twitter”:“xyz dot com”, “facebook”:“pqr dot com”} I want to use the “keys”, viz, blog, twitter and facebook to extract abc dot com, xyz dot com and pqr dot com respectively. So if I have the key “blog” in a formula, I want to store the corresponding data ie “abc dot com”. Any ideas how to do this?