Formulas

I have a “Next Year End” date field for each of our clients and then I have fields for each of the 4 quarters to populate dates in various other tables. I am trying to create a formula that will take automatically choose the last day of the month when I use this formula: DATEADD({Next Year End}, 6, “month”) - it basically brings me to October 30th, not October 31st. Does anyone know what I’m missing? I’ve been looking everywhere in here…It doesn’t seem like this should be so difficult unless I’m totally not understanding :slightly_smiling_face: Thanks in advance!

I am reconfiguring my budget spreadsheet and am having trouble with an IF formula. I want to set up the base so that I can select if something is revenue or an expense, enter the dollar amount, and then in the next column over (Debits and Credits), it will automatically add a (-) negative to any item that is marked as an expense. Here is the formula I was using: IF({Trans Type}=“Expense”,{Transaction Amount}*-1)&IF({Trans Type}=“Revenue”,{Transaction Amount}*1) The formula is messing things up somehow because it won’t recognize the output as a number, and therefore will not let me format it as currency. Instead, it says: Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date. I want this column to be formatted as currency and show a running total. Can someone

Hi, I am creating a client job tracker and would like to autogenerate 4 digit numbers, eg. 0001, 0002. Can someome please help as I can only get a single digit or decimals so far. Thanks Daz

Hi, I have a list of cities and states, and I want to extract only first state initials. How can I do that? Thanks

Hi there, I’m trying to figure out a way to take 2 date inputs (shift start and shift end) to output a duration and I’ve done this using “DATETIME_DIFF({Shift End}, {Shift Start})”. However the system we use for our company relies on daytime hours and night hours being paid differently, so ideally we want the end duration to be split between day and night where the crossover is 7AM/7PM. For example if someone started their shift at 0500 and finished at 2300 they’d work 12 day hours and 6 night hours. Would this be possible to configure within Airtable’s capabilities? Thank you in advance.

I’m trying to use a formula to calculate the number of years between two dates, namely the start and end dates of a job/position – while accounting conditionally for when ether of those fields are blank. Thought myself rather clever with this formula, but sadly it’s not working. Can anyone help me figure out what’s wrong? IF( {Position Start}=BLANK(), "", DATETIME_DIFF( {Position End}, IF( {Position End}=BLANK(), TODAY(), {Position End}), 'years' ) )

I’ve been fighting this and researching long enough. here is what I currently have that doesn’t work: IF({Referral Name}}, “Field is Not Empty”),{Commission}{Referral %},"")* Explained: If Referral Name field is not blank or empty, Calculate Commission field multiplied by Referral % Otherwise, leave return a blank field. Help! :slightly_smiling_face:

Hi Airtable! I am trying to figure out how to create a formula for a string. I would like to remove everything after the first set of numbers, with no spaces: 1234 56.jpeg 1234a 56.jpeg to: 1234 1234a Can someone help me? Formulas are not my strong suit. Mary

Hi! First of all, I am french so I hope you’ll understand my question. :hugs: I’d like to calculate how long has an employee been with the company, so : since entry date (fr: date d’embauche) until now/today. I already have a formula that calculates the number of years (fr: années) and another formula that calculates the number of months (fr: mois) but I’d like to have these informations in one formula. Is that possible ? Thanks !

I thought this would be fairly simple but then it turned out to be a little more complicated, and I’m not sure if this is possible to do without Javascript. I have a work start date, I know how long the work is going to take (in hours) and I want to calculate an estimated End date. But different days of the week has different shift hours fx. Mon-Thurs has 10 hours shifts, Friday 9 hours and Saturday 5 hours. Is it possible to calculate an end date, with these informations? I’ve tried to use play around with WEEKDAY() and DATEADD() but nothing sofar has come close to any solution. Or is this only possible in JavaScript?

Hi there! I have the following categories in my table: Task Due Date, Weeks From Launch (Formula Field) and Launch Date. I want to write a formula in the Weeks From Launch category where if the Task Due Date is blank, then record cell stays blank, otherwise it uses the Launch Date and Task Due Date fields to calculate weeks from launch. My current formula (returning an error message): IF({TASK DUE DATE} = “”, “”, DATETIME_DIFF({TASK DUE DATE}, {LAUNCH DATE (from PROJECT)}, ‘WEEKS’) Any suggestions? Thank you!

Hey All! I am trying to check to ensure that all timesheets have a corresponding scheduling item but my FIND formula is only working half the time. Any help would be appreciated! IF(FIND(Timesheet,{Scheduled Dates (from Task)})>0,"",“Missing!”)

Hi All, I have a field which is pulling in from JIRA and displays like this: 154, 152, 157, 155 There could be more or less numbers. What I need is to create a new field which is the highest number in this field. (So it will display 157 in this case). The issue is the rollup feature is displaying 0. I assume this is because this is being viewed as text. Any ideas how to solve this? Cheers Simon

Hi Community, I want to use a formula to calculate the age of a product DATETIME_FORMAT(TODAY(),‘YYYY’) - {year produced} Some fields of {year produced} are empty, so the formula comes back as the age of 2022 years old. Is there some way to exclude the empty {year produced} fields? The year produced, has the number field type. Thanks in advance!

Hey everyone! Fairly new to airtable and still learning about automations and formulas. We are creating a fairly complex app for medicinal products. In our base we have a products table and an indications table. An example of an indication would be “Pain” - This is linked to a stacker front end. What I’m trying to achieve is to be able to count how many times a single indication has been given to a product. For example: When someone is adding an indication such as “Pain” to a product they can see “5 others added this indication”. and once added it’s then updated so that it appears as 6, 7 so on. I have been able to automate a count which listens for when an indication is added and ticks up however this doesn’t work because each time any indication is added or removed it continues to tick up. Trying to figure out how to make it dynamic so that it can update and change for any product. Any help would be greatly appreciated

I am trying to automate the calculation of an award based on an age category (“PVSA Age Group”) and a number of hours volunteered (“Total PVSA Hrs.”) (see chart). I think I need nested IF statements with an AND, but not sure. It’s not working so far (because I have a day job hello.) I need an output based on these two variables. Here is the first part of my formula, attacking only the second row (we have no contenders for KID category in the first row, just the remaining 3 rows): IF( AND( {PVSA Age Group}=“Teens”, {Total PVSA Hrs}<50 ), “No award” ) & IF( AND( {PVSA Age Group}="Teens”, {Total PVSA Hrs}<=74 ), “Bronze” )& IF( AND( {PVSA Age Group}="Teens”, {Total PVSA Hrs}<= 99 ), “Silver” ), “Gold” That doesn’t wo

I have used this formula to separate data that is simple 4 letter abbreviations and it works perfectly. However, the data is also associated with dates and times that I need to separate and have readable and separate in an email automation. When I use the above method, the dates (which read fine in their native field) come out looking like this Has anyone run into this before or have a solution?

I am trying to create a quality score for a series of job applications. I have a series of fields returning a count of how many “excellent,” :“good” “poor” evaluations each application received, expressed as a numeric value. ie Count-Excellent Count-Good Count-Fair etc. First I want to multiply each field by a unique value in order to build up a score, ie if an application got 2 “Excellent” ratings (shown in Count-Excellent as “2”), give it 5 points for each Excellent rating and thus returning a value of 10. And 4 points for each Good, etc. Then I want to add all those values together, producing a combined score, and THEN… divide by the number of reviewers, which is stored in a field called ReviewerCount and is also a number. Basically I want to do something like ((Count-Excellent * 5) + (Count-Good * 4) + (Count-Fair * 2)) / ReviewerCount and have it produce a score, but I seem to be getting hung up on the parenthesis and brackets

I am trying to use REGEX_EXTRACT to convert a username into an Email address. For example: WSSUMITS\smithfred => smithfred, then I can add the @domain.com to the result. The regex formula I came up with, through trial and error, is: REGEX_EXTRACT({Asset Last Logged In User}, '^(?:WSSUMITS)?([^:\\/\n?])+\\') However, I’m getting ERROR in the field. I don’t have much experience with regex. Where am I going wrong? Thanks in advance.

hi, I’m new to airtable and not much of a techy person so even when I read some of the comments for how to do a running total, I’m still so lost… I’m trying to create a monthly roll up to see how much money I should have at the end of each month but rolling up from the last month total. I am able to get as far as getting the Income - Expenses = Total but I tried using Previous Row function to get last row total to try to add it to next month’s total but it tells me it causes a circular reference. Or is there a way to add last month’s total with this month’s income? I’m at my end on trying to get a running total. HELP anyone… here is the view of the table… I need the previous row to be the last column roll up. I can’t set it up since it gives me circular error so only been able to use Delta for previous row to test it but it’s not the right value I need for the running total. how can I get the last column to be the running total?

I am trying to solve the following problem. I have thousands of registration numbers that always start with a “T” and are followed by 10 digits. This is how they are delivered to me T3090728379. But they all need to look like this T-309072837-9 which translates to the following format T-[9 digits]-[1 digit] The last digit can be anything between 0-9. What formula could I use to reformat these fields in Airtable? I managed to substitute “T” with “T-” but I do not know how to get a hyphen in front of the last digit. Is this something one would use the LEN() function for? (I have never been able to fully understand how this works…) I’d appreciate any help. Thank you so much in advance. This is how far I got: IF(STRING,SUBSTITUTE(STRING

Okay, so I’m new to AirTable, trying to move away from Excel sheets :slightly_smiling_face: I’m creating a forecasting tool for my sales department. For each project we forecast the number of products that we will sell. For my purchasing department, I’m creating another tool that will use the summarized number of forecasted products in calculations. So for example Project 1: Product 1 - 30 pcs. Product 2 - 10 pcs. Project 2 Product 1 - 5 pcs. Product 2 - 10 pcs. I need the sum of Product 1 (35 pcs) and Product 2 (20 pcs) available for further use in another table. How can I do this? These qty I would like to summarize in the Product table.

I’m relatively new to airtable and i’m working on a tracking and costing system for the project I am working on. I am creating security schedules which are usually the same for monday through to friday. they change on saturdays, sundays and public holidays. I want to count the number of saturdays and sundays in a given period to set the schedule for those particular days but the starting point is knowing how many there are… can anyone help out with this? thanks

Hi, I need help to split this string into four pieces and put them in four new columns… Maybe split two times with different delimeters? {“email":"mail@gmail.com”,“firstname”:“John”,“lastname”:“Hong”,“phone”:“1234567”} Column 1: mail@gmail.com Column 2: John Column 3: Hong Column 4: 1234567 Thanks in advance!

Hello, I am looking for a solution/formula that will generate in a Multi-Select field all unique dates between ‘start date’ and ‘end date’. I need that solution to generate a report with rollup fields and calculate the number of inhouse guests on specific dates, for exemple in hotel room management, or vehicles car rental. Thanks