Formulas

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?

I have a Learning Course table that tracks courses with either a single session or dual sessions via checkbox fields. I have links to fields in this table that identify whether an enrollee has completed the relevant sessions(s). From there I have a checkbox field that indicates whether, based on the attendance, whether an enroll has effectively completed the course. I tested a formula field that runs the checkbox field for the dual session completion, but I’m struggling to create a formula that accounts for the single session field. I initially thought it was an IF(OR(AND())) type of configuration but nothing seems to work. Here’s what I tried: Works: IF(AND({Session 000 Output}>0,{Session 001 Output}>0),{<TRIGGER> Completed Course}=1,{<TRIGGER> Completed Course}=0) Doesn’t Work: IF(AND({Single Session}=1,{Session 000 Output}>0),IF(AND({Dual Sessions}=1,{Session 000 Output}>0,{Session 001 Output}>0)),<{<TRIGGER> Completed Course}=1,<{<TRIGGER&

I have fields with extracted text from a contract and what I need to do is extract every vehicle registration number in the text field. The formula I have will only extract the first valid number. Thanks in advance!

Hi, I am trying to prefill a form using the following formula: CONCATENATE({Applications URL}, ‘?prefill_Which+role+are+you+applying+for?=’, {Name of opportunity}) The ‘Name of opportunity’ field contains more than one word, but the formula is only pulling the first word. Here is an example of one form’s URL: Airtable Apply to our roles today! - Airtable Fill out the "Apply to our roles today!" form quickly and securely on Airtable. When the ‘Name of Opportunity’ is actually ‘Logistics, Freight and Warehousing - DSV’. How do I ensure that the form populates with all the words? Thanks!

Hi Airtable Community, We are using the “enable rich text formatting” on a “long text” field, using bullet points to indicate points of attention or statuses. Is there any way to use this rich text and do the following: Filter out first / last 3 bullet points by using a formula? Filter out items under a bullet point matching a certain category, eg. text in bold - by using a formula? Screen shot attached where I’m looking if I can filter out basis eg. “Week 13” or filter on bold. Many thanks!

I am attempting to collect data from employees that tracks incident reports over time. This roughly works as follows: Form1: "Report New Incident" This form collects information about an incident such as location, date, classification, etc. and assigns it a new ID. ID | Date | Location A | 1/1/19 | Plant X B | 1/15/19 | Plant Z Form2: "Update Incident" This form asks the user for an incident ID and then some additional information is added to the original incident (new information or updates to old information). ID | Date | Status A | 1/3/19 | In progress A | 1/5/19 | Solved The idea is, the employee who is updating on an incident does not need to reenter information (such as location, classification), but should provide some updated information (update time, current status). Having two tables ensures they contain unique information with the exception of ID. Unfortunately, I can’t join together based on ID with some sort of JOIN query. Using lookups partially solves this, but then it g