Formulas

Hi all, I have a formula field that is used to output a date that is +28 days from a separate date field. Here is my current formula:   DATEADD({470 Certification Sent Date}, 28,'days')​  I need this field to always be 12:00a in the 'Americas/New_York' time zone. Is it possible to create this output without accounting for the date/time/timezone the original entry is added in the "470 Certification Sent Date" field? If so, what do I need to append to the original formula?Thanks for your help!

I was wondering how can I just show the days from any given date?

Hi all, Hoping someone can help. I'm trying to create a time sheet where the user inputs the hours & minutes they worked in 15min increments - so 1.25 (1 hour 15 mins), 1.5 (1hr 30 mins) etc.How do I get this to appear as 1hr 15 mins, 1hr 30 mins in an hh:mm format without asking them to do their time sheets in seconds and also have the ability to roll up the totals?Ideally they would not be tracking start end times using date fields. Just a box yo input a decimal. Example:  This is a platform I've used for timesheets in the past, Synergist Thank you!

Hello the community!I'm trying to do an extraction from a text but I can't get it My Initial text looks like that :   RP-34, CP-4554, IO-34, REQ-455, DK-344, REQ-44, REQ-404, FG-34   and I would like to export the list of items with REQ to have this result:    REQ-455, REQ-44, REQ-404  Does anyone have an idea of what formula to use? 🙄Thanks a lot! 

Hello all,I am new at this and english is not my first language, but i'll try to explain.I have a number of columns with numbers in it.I want to make a formula that if the number is between 2000 en 3000 in that column then in the formula colomn it states 50 bonus and if the number is between 3000 en 4000 it states 100 bonus and when not met is says no bonus.So:Colomn 1          Formula field1899               No Bonus3999              100 bonus2545               50 bonus So far i have this formula:IF({Jul} < 2000,"No bonus",IF({Jul} >= 2000

Hi there, I'm trying to get value on my main table from another table based on conditions I've defined in the main table and that will meet the parametres from a second table.I've worked with thw ways in order to tackle that issue. First is to write a formula able to do that. IF(AND(Table1.UOs = Sync.UOs,Table1.SP=Sync.SP),Sync.Name)The other way investigated is to go through script like the one below. Still working on it to include to loops for the two conditions to meet. let mainTable = base.getTable("Table1");let mainTableRecords = await mainTable.selectRecordsAsync({fields:["UOs","SP","Test Script 2"]}); let lookupTable = base.getTable("Sync");

Hey All,I know that Airtable is based off of fields and rows, so it's inherently different, but I wonder--similarly to excel, Is there any way to set up a formula in Airtable so that if 'x' condition is present in field 'a' and 'y' condition is present in field 'b', then field 'c' will display as 'z' for the corresponding row?For example, can I say, if budget is < x amount or > y amount, budget size should display "Big" "Little" or "Small?"Then, based on the info under budget and budget size, could I input logic that says, "If Budget is < 'x' amount or > 'y' and 'Budget Size" is 'x' 'y' or 'z' then What to Do should display 'a', 'b' or 'c' for a under that field?Please help! Thanks!

Hello my friends. I have a seasonal task calendar that is organized by project WEEKS. Our team works from Week 57 until Week 30. Tasks are entered into the Primary Column with an associated Start Date and End Date. Currently, the Project WEEKS are set up as tasks as well to allow the Project Managers to group by project week. When team members add a new task, they have to manually add the corresponding project week. I don't have to tell you what a chore that is. As the team Airtable person, I end up cleaning up the missing or erroneous data.In the screenshot below, you can see that I've started experimenting hoping that it would lead me to a solution. Here's what I need in prose:If the task START DATE and END DATE fall within the start and end date of a particular project week, return that project WEEK (currently in TEST Weeks). Do I need to reference the project weeks in another table? Thank you in advance for your help.

Hello guys,I am an extreme beginner,I have my data as a date in the incorrect format, I'm looking to auto-convert each figure into a specific "date" category.Can someone please explain how to do so?Thank you so much in advance

Dear Airtable Community, I have a board with records organized with date field and category list.1 - I would like to count the number of days in this record. If 4 records are posted with the same date I want it to count 1.Ex : 01/01/2022 I have 4 records registered at this date, I would like to return 1 day for those 4 entries.2 - I would like to get the number of records registered per date, I tried to use Interface to do it but could'nt extract the number of dates Step 1.Thank you for your help.Best regards,Delphine 

Hi This is a File Path from Google Drive integration.BOOK 📘/CHAPTER/PAGE/PARAGRAPH/SENTANCE/WORDI am trying to create formulas to split the File Path into separate fields for each folder. I can get the name of folder and the parent folder but want to separate it all out so I can see subfolders etc. I have been trying variations of LEFT/RIGHT/MID and LEN but can't quite work it out.Any help much appreciated.Thanks, Maria  

Hi Guys,I am trying to wrap my head around regex formula but can't figure out what if there are two patterns in the string. Basically I want to extract the number after the bracket but before the "+".Current value : /Date(1676537746330+0000)/Desired value : 1676537746330 Thanks

Hello Airtable Community,     Question: My main field (first left field) is the "Date", next field is "Day of the Week" (single select). Is it possible to automate "Day of the Week" field so it would automatically choose the day based on the calendar date input in the "Date" field?   Thank you in advance for your help

I'm using make.com and have a connection to Airtable and am trying to do a simple search on a table. I can't find a guide on Make or Airtable on the syntax required in Make's Formula field.I would like to do something like the following logic{Email}='1.email' AND {Date} >= CURRENTDATEWhere Email and Date are fields in the table I want to search. I can't seem to find the equivalent of CURRENTDATE in airtalble's Formula Playground or elsewhere.

Is possible to have a unique id number from autonumber function across all tables?, For example: If I create a new record on table A, that record must be 1; but if I create another new record on table B must be 2

Hello dears,I need help please to do the following formulaIf multiple cells in the "Star" column matches each other in text and the matched cells has the options "in office" or "stock" in the "Status" column then mark all cells as {show}If multiple cells in the "Star" column matches each other in text and the matched cells doesn't have the options "in office" or "stock" in the "Status" column then mark all cells as {hide}The below image should clarify the need please i need help 

Hello,I'm wondering if anyone has any recommendations on how to achieve the use case below. I've seen VLOOKUP scripts and I've also seen automations, but not sure which would be easier given the criteria.Table A has each Product and State pair listed as its own row (as there are other columns of data applicable to that pair). The Product will have as many rows as necessary, depending on how many states it's sold in. The Status column says if it's approved to be sold in that state.Table B needs to be a summary of all of the states that each Product is sold in and the status of the approval, where each Product has one row and the States are now listed as columns.Table B needs to lookup the Product name in Table A, find the State name in each row and bring the Status in that row over to the matching State column in Table B.It would be great if ea

hi, asking for help,I made a database of my office documents and i need a formula for due dates that is due on 16 months, however the due dates will correspond to two （2） date fields, ex. date A and date B, the condition is this; if date B is not empty, due date will correspond to date B + 16 months, but if date B is empty, due date will correspond to date A + 16 months.I hope someone help me, thank you!

I have created a Rollup field in my Contact table which searches within the Touch Points linked table and rolls up the 'Start Date/Time' field using the 'MAX(Values)' aggregation formula. My challenge is that I am unable to get a date which I can then filter to appear inside my Rollup field. The Formatting tab does not have the option for date as per screenshot:https://share.getcloudapp.com/X6uKqr7Qhttps://share.getcloudapp.com/9ZuKv2yyAny input would be appreciated. Video included below for more context if necessary:https://share.getcloudapp.com/rRugpPzAThank you!

Hi,  In my database I have several fields that are calculated automatically with a formula, but I want some of these important date in a calendar.  But the Calendar only allows me to use a Date Field, how can I "force" the calendar to take the formula date ? This is my formula fieldDATETIME_FORMAT(DATEADD({Date de départ}, -60,'day'),'DD-MMMM-YYYY') Thanks for your help !

Hello Airtable Community! 🙂So... I am rather new. I have a formula that works fine, except that occassionally the result is a negative number.How can I display the result as "ZERO" if the result is less than zero (i.e. a negative number)?I read somewhere that MAX should do the trick... but not working.   IF({Total Sold}> 0, MAX({Total Sold}-({Products Completed}+{Total qty on hand})), 0)   P.S. As mentioned, I am new, so if you do write out a quick formula, could you please use MY actual field names? 🙂please advise, and thank you in advance!DJ

Hi, I'm working on a task list with two date fields - Action Date (the date to focus on something) and Due Date. Not every task has a due date. and not every task has an action date. Is there a formula I can use to pull either the earliest of those two dates (if there are two) or whichever date there is (if only one)? Thank you!

Hi! Is there a formula (or other solution) to remove extra whitespace within a string? My formula right now looks like:  SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM({name})," "," ")," :",":"),” .”,”.”),” ?”,”?”),” ,”,”,”) This works great to remove whitespace before/after the string using Trim and then remove double spaces and spaces before punctuation using Substitute. I'm wondering if there's a way to also remove instances of 3 or more spaces within a string without separately writing each possibility (3 spaces, 4 spaces, etc) as substitutes.Thanks for any help!

Hi!I'm having such a basic issue and can't figure out what I'm missing. I believe this should be a very basic IF statement: IF {field} is not empty, show this stuff. However, I've tried using {field}, {field}!="" and {field}!=BLANK() and even where there is nothing in {field}, text is showing. Here's the simplified formula I'm using to figure out what is going on (substituting "the text" for what will really be there):IF({abr formula- all},"the text")Here you can see that even if {abr formula- all} is blank, "the text" is showing up.Any help appreciated!

Hello everyone! So first off I have this formula field:   DATETIME_FORMAT( IF( DAY({Tilføjet d.}) <= 10, DATETIME_PARSE(CONCATENATE(YEAR({Tilføjet d.}), '-', MONTH({Tilføjet d.}), '-11'), 'YYYY-MM-DD'), DATEADD({Tilføjet d.}, IF(WEEKDAY({Tilføjet d.}) <= 3, 3 - WEEKDAY({Tilføjet d.}), 10 - WEEKDAY({Tilføjet d.}) + 3), 'day') ), 'DD/MM/YYYY' )  The name of that field is 'Kan laves d.' and shows me the date of the next coming wednesay based off of the date the record was created. So for example if the Record was created today, it will show me 25/2/23. So what I want to make now is a new formula field, that will add a green circle for all the records that can be made the next coming wednesay, and only for those.  Can someone help me with this?