Formulas

After through the documentation on DATETIME_PARSE() I’m not seeing the timezone respected when used in conjunction with either SET_LOCALE() or SET_TIMEZONE() for conversion of a string field containing date and time information. What am I missing here:

I am analyzing data from an evaluation form where all field are mandatory. When reporting them using Page Designer I want to omit any entries where the person has typed n/a (with any combination of capital or lowercase letters). My first thought was that there needs to be an IF statement in there some where, but where? Currently I have: ARRAYJOIN(ARRAYCOMPACT(value), “/n/n”)

I have the following phone number +33661291700, do you know a formula that converts it to a US phone number? (e.g: (415) 555-2671) Thank you

Hello All, I am trying to convert a concatenated text cell into various records, split by the semi-column. Ideally, an automation would somehow split the cell contents so I can return the linked lookup value for both Optimisers, and Double Storey and their relevant costs in another lookup column. Is there a way I can split the contents into separate records for the pricing data column?

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

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

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

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

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}/{I

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.