Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,944 Topics
  • 18,246 Replies
5944 Topics
H
hrwatkins86New Participant
posted in Formulas
calculation on pivot table

I'm trying to calculate return rate (items received/ items delivered),  but aggregate them by day.  However, there are multiple receiving lines for each event, and only one input for items delivered.  I'd normally do a formula based off a pivot table but I'm not sure how to do that in Airtable.Here's an example of the table I have Event NameItems ReceivedItems Delivered Timestamp 1x1040 Timestamp 2x20  Timestamp 3y4050 What I need is Line 1 & Line 2 added together for Items  Received, and divide that number by line 1 of items delivered. How do I achieve this? 

161
Arthur_Tutt
K
kjhbjkljn5gfqrkNew Participant
posted in Formulas
Need a formula to return a value from multiple records based on two variables

Hi,I have a base where we I need to return the lowest value possible value for items that share a unique identifier (this is generated for each record and only some overlap) AND the same quantity. For example, say I had 4 records that generated their identifier to 101A, with quantities of 200, 300, 400, 500. For All of these records, I need a field that returns the minimum quantity (200) for each record with that matching ID ; and will work with possibly thousands of unique identifiers. I thought this would be an easy formula, but cannot seem to make this function whatsoever. 

142
Arthur_Tutt
S
SmoteNew Participant
asked in Formulas
$/hour formula only giving (incorrect) cents instead of whole dollar amount?

Good morning!I am trying to determine how much I am making per hour at various markets that I vend.I input my markets with a start date+time field and an end date time field and have that calculate out to a duration field.       I also have my earnings field (which is what I made minus event fees, also calculated out).  *it's the "Minus Fees" column I'm using for my final calculationsLogically, I should just be able to do my earnings / duration = dollars per hour amountor in my chart's terms: {minus fees} / {duration (show)} = $ per hour amount  However, instead of getting a nice dollar value, this is what I see:I feel like I'm missing something incredibly obvious here because obviously,$1,124.17 / 7 hours does not equal $0.04 cents. It should be $160.56.Can someone spot where I've gone wrong?   Thanks!    

124
S
airballer86
airballer86Known Participant
asked in Formulas
FIND formula to identify one or many issues in a URL

I am trying to use the find formula to identify one or many issues in a URLFor example in the field Link URL I have two urls; one with one issue and one with multiple issueshttps://www.google.com/workin'-late.htmlorhttp://www.google.com/workin'-late.htmlAny help would be appreciated. Here was my first go at it:IF({Link URL},IF(FIND("http://", {Link URL}), 'missing https',IF(FIND("'", {Link URL}), 'remove apostrophe',IF(FIND("http://", {Link URL}),FIND("'", {Link URL}), 'missing https and remove apostrophe'))))

134
S
S
Scott_BrastedInspiring
posted in Formulas
Calculate date difference in years AND months, and 1 more question on this formula

I want to calculate the difference between 2 dates. One is the purchase date of a condo and one is the sale date of that condo. I need the number of years and months to figure out refunded common charges at closing. I have the formula working for years or months. But can it be both? If not, anyone have experience in this scenario and their solution? And in the same formula, the field shows NaN if there is no data in one of the fields. For current owners the buy date will be populated, but the sell date will be blank. How do I tell the formula to leave the field blank if one field is blank? Thanks in advance for any help on this. Best, Scott

293
A
MilhoanDesign
MilhoanDesignInspiring
asked in Formulas
Formula and Calculation Help

I am trying to solve the problem that I have illustrated in the image below. I am trying to create/calculate (see image) {Rate} and {Day Total}. In the below example, the first record for "Old Cypress - Weaned Calves," if the "Weaned Calves" == "Pasture" then pull in the value .70, and then multiply that value with the "Inventory" of 450. This will result in a "Day Rate" of $350. Any help will be appreciated! Thank you! 

132
MilhoanDesign
K
Kevin_AustinNew Participant
asked in Formulas
IF AND For Two Conditons Not Working

I'm trying to get two conditons from two fields that contain certain text that if True produces a duration value.  I can get it to work work one condition but when I add the second it will not work.  Can someone see what I'm doing wrong? This Formula works: IF(FIND("ABC",Origin),3600,0) But when I add the second condition it shows all 0's on the fields that contain "ABC" and the fields the doesn't contain"ABC" gets the 3600: IF(AND(FIND("ABC",Origin)),AND(FIND("New",{Lane class}),3600,0)) Thanks 

93
Kenneth_Raghuna
A
ARTHUR_BENNETTInspiring
asked in Formulas
Trouble with IF

What is wrong with this IF statement?  IF({Sonrise Home} = "CH", "Sonrise Children's Home"), IF({Sonrise Home} = "MIR", "Mirembe Cottage for Girls"))"Sorry, there was a problem saving this field. Invalid formula. Please check your formula text."  

152
A
L
loeweNew Participant
asked in Formulas
Wrong colors on Progress Bars

Hi everyone,I have an issue with the color formatting for Formula Feilds displayed as progress bars, it seems buggy:With decimal percentages the conditions are not recognized properly, I have the following conditions on a fieldbut the results are unfortunately wrong for values that are near the "breakpoints".Any suggestions?Thanks

228
L
1
1ikigaiNew Participant
asked in Formulas
Date recurring on a yearly basis every year?

Hi fellow Airtablers!Could anyone suggest how can I make the ''Next charge'' date field recurring on a yearly basis every year if the toggle in ''Frequency'' is set to ''Yearly''? This dilemma is driving me nuts 😞 Right now, I just have a formula in that field:DATEADD({Latest charge}, 1, 'years')But it's not ideal because I would need to manually update ''Latest Charge'' field with the previous year every year.Ideally, I'd love to solve this with a formula because I'd have lots of entries, and it may be a bit cumbersome to create automation for each and every entry.But if automation to update the latest charge is absolutely needed, what is the best way to go about it/set it up? 

475
1
Cody_Winchester
Cody_WinchesterInspiring
asked in Formulas
DATETIME_PARSE operating incorrectly

Hello,I've encountered an issue with the DATETIME_PARSE function not correctly converting a concatenated date and time from two fields. Is there something I'm missing?

92
Cody_Winchester
J
JennaCNew Participant
posted in Formulas
Creating a blackout date in Airtable calendar

Hi there,I'm working on an integrated communications calendar for the upcoming year, and there are some dates where we do not want multiple communications going out. So, I'm trying to figure out the best way to either black out the date (i.e. if someone enters the date no comms should go out when creating a record or filling out a form to create a record, they are forced to choose another date), or at least have a prompt that says "no communications this day, please select another date." Unsure if this would be a function, automation, etc. -- slightly new to Airtable, so any insight would be helpful. Thanks! 

241
AirOps
E
Emily_McLainParticipating Frequently
asked in Formulas
Extract the first date from a string of dates

I have a multipart task that I think can surely be accomplished with a few formulas. I looked around the forums for a while and I think I have an idea but I haven't been able to put it all together yet! Basically I have lookup field collating all the dates that a certain client has ads scheduled across multiple products. For our scheduling purposes, the first of each month have a "- [Month]" text added. I want to identify the first date among this group of dates, for a notification automation to let the team know when this client's contract starts. Using a Formula or Rollup field I can transfer that into a string separated by commas. From here, my general understanding is I need to:> Strip out the text so its just dates (I tried REGEX_REPLACE but it also removed everything after the first instance of text. These type of coding functions are really over my head)> Format the dates into YYYYMMDD numbers that can then have the MIN function applied to find the earliest

1382
E
A
Andreas_TystadNew Participant
posted in Formulas
Create a YouTube URL from fields

Hi,I want a URL field to create automaticly with YouTube videos from Field A and Field B.Field A is song title, and Field B is artist, and I need a URL that automaticly finds the music video of that song.Let's use Hello - Lionel Ritchie as an example. I do not need the URL to be YouTube Search (https://www.youtube.com/results?search_query=hello+lionel+richie) But i need the URL to guide me straight to the  music video: https://www.youtube.com/watch?v=mHONNcZbwDY  Is this possible? 

74
S
C
cydNew Participant
posted in Formulas
Recurring Dates Yearly

I would like the holidays to be recurring dates yearly. This is to be included in the same table as the leave tracker so that everything is in 1 calendar view only.

63
Databaser
S
Scott_BrastedInspiring
asked in Formulas
Formula to calculate the difference in fields

Hello, I have posted about 15 questions this board and I have always received a solution my problem. So thank you all very much. I think I have a really tough one this time. I have a base to manage memberships and donations and legacy gifts for a not for profit. There are 6 membership levels with specific dollar amounts attached to them. So all is well as long as a person pays an amount that corresponds to an existing membership level. But when they pay an amount that is not a preexisting level, I have to assign the difference to a donation.I have a Currency field that records the actual amount paid. I have a formula field (thank you Alexey_Gusev) that shows the membership level closest to the payment amount. For instance, $35 would show as an Individual membershi which is set at $25. I need to then add the remaining $10 the donation field. The next level is $45 for a Family level. To be completely honest, I have no idea where to even begin with this. Due mostly to the h

496
S
S
Sidney1983New Participant
asked in Formulas
Generating a Checkbox in a Formula Field from Multiple Select Field.

Hi there;I'm very new both to Airtable and formulae, so I hope this is just a beginner's issue and is not a function or possibility currently beyond the possibilities Airtable provides.I have a formula field (Sim Required?) that I would like to populate a green checkmark from a combination of possible options in the multiple select field (TikTok / Instagram / Router).The multiple select field has 4 options:TikTok 1, TikTok 2, Instagram and Router.I would like the green checkmark to appear under these conditions:TikTok 1 only is selected.TikTok 2 only is selected andBoth TikTok 1 and TikTok 2 are selected.I've managed to have the checkmark appear only when TikTok 1 is selected; when I try with only TikTok 2 or a combination of 1 and 2, the green checkmark doesn't appear.Thanks for any help you provide, 

144
S
H
harimonthstayzInspiring
asked in Formulas
Nested if for dates

Hi,I have a table to have a list of equipments and when they were cleaned. Apart from equipment details I have these fields:Last maint date (a date field - to be manually entered)Next maint date (formula which returns a date 120 days after Last maint date)Actual cleaned date (date field - date on which an equipment was actually cleaned - to be manually entered)Status (Formula - I want this to be evaluated for 3 conditions and return text phrases accordingly)Conditions:If the 'Actual cleaned date' is greater than 'Next maint date' then return 'Delayed' in StatusIf the 'Actual cleaned date' is equal to 'Next maint date', then return 'Cleaned on time'If the 'Actual cleaned date' is lesser than 'Next maint date', then return 'Cleaner early'  I tried this nested if IF({Actual date cleaned}>{Next maint date},"Delayed",IF({Actual date cleaned}={Next maint date},"Cleaned on time")) But it is not returning any phrase as needed.Kindly hel

75
Arthur_Tutt
S
Scott_BrastedInspiring
asked in Formulas
Nested IF AND Statement

It seems the more I learn here the more ideas I come up with that I need help with.I have a membership payment table. There are 6 levels of membership.Individual  $25.00Family  $45.00Patron  $60.00Sustaining  $100.00Benefactor $250.00Sponsor  $1,000.00I would like to write a formula in the membership level field that fills in the membership level in the membership level field using the amount entered in the amount of the membership payment field. There are 6 membership levels as above. So if they pay $35, then it would put in as an Individual membership, etc. The $10 difference would but put in a field of the donations table as a donation. I will start that formula when I get this working.This is what I came up with but it has an error. Am I missing an OR statement maybe?Many thanks, Scott IF(AND({Amount Paid}=25,{Amount Paid}<45),”Individual”,IF(AND({Amount Paid}>25,{Amount Paid}<45),”Family”,IF(AND({Amount Paid}>45,{Amount Paid}<60),”Patro

453
Alexey_Gusev
L
LindsayRidpathInspiring
asked in Formulas
Extract/filter checked items from a rich text field

Hello, I'm trying to find a function that will pull all of the long text; rich field checked boxes away from any unchecked boxes. I've found the solution linked below and it worked great for the opposite problem. Does anyone have a formula to solve pulling only checked boxes? https://community.airtable.com/t5/automations/extract-filter-unchecked-items-from-a-rich-text-field/td-p/27036Subtasks are all our tasks. Next steps are unchecked tasks. Activity completed should be only checked boxes from the subtasks table.  Thank you!!  @TheTimeSavingCo @Brian_LWA 

92
L
S
Scott_BrastedInspiring
asked in Formulas
Help with IF and DateTime_Diff

IF({Last Renewal},DATETIME_DIFF(TODAY(),{Last Renewal},'days') > 365,"🔴","🟢") The above formula, without the first {Last Renewal}, works fine. But I have records that are empty and I want them blank. I obviously am not constructing my syntax correctly. I want to say that if {Last Renewal} is not blank, then do the following. as I said it works if take out the first {Last Renewal}. I'm sure it is simple. I just don't see it. Thanks, Scott

164
S
N
NP89_2New Participant
posted in Formulas
Trigger Reminder Formula

Hi,Would like to set up a Trigger Reminder with the formula;IF(AND({Reminder},NOW() >= {Reminder}),"Trigger Reminder")But instead of NOW, would like to customized it to xx hours from created TIME, specifically. Have only seen customize formulas for a certain date or number of days but nothing for a certain time period. Any help would be greatly appreciated.

31
Sistema_Aotearo
L
loukasParticipating Frequently
asked in Formulas
URL Filter: works by date - breaks by time

Hello! This filter does not take into account the time part when applied in URL: filterGreaterOrEqual_Date=2023-10-17T22%3A00%3A00.000ZExcept for when I set it at 22:00 where it filters OUT all records with date October 17, 2023 and "allows" them back in when I set it to 21:59.Other settings such as 06:00:00.000Z or 18:00:00.000Z show every record of October 17. Dates on my table are:17 October 2023 00:00 UTC17 October 2023 00:00 UTC17 October 2023 10:00 UTC17 October 2023 10:30 UTC17 October 2023 22:30 UTCIf I remove the time altogether it catches Dates very well: filterGreaterOrEqual_Date=2023-10-17 works fine. Any Ideas please? 

61
S
W
Wendy_YelsikInspiring
asked in Formulas
Numbered Bullet List

I am trying to auto-number a group of records for one person in a table with multiple people listed with numbers 1,2,3, etc.. In Excel, I would use a formula to add one number to the above cell, and it would number all rows sequentially. I usually manually number each record, but I am hoping a simple formula would number the list.Here is a screenshot of my table consisting of the records I need based on a filter.  The row with # in the title is where I would put the formula.  Any suggestions? 

562
W
W
Wendy_YelsikInspiring
posted in Formulas
Extract numbers from field into a new field

My field has a name followed by a number - SAM ADAMS (1234).   I need to put the 1234 in it's own field.   What is the best way to write a formula to do that?   

325
ScottWorld

Badge winners

  • First Topic
    JenGhas earned the badge First Topic
  • Welcome
    JenGhas earned the badge Welcome
  • Welcome
    Krism84has earned the badge Welcome
  • Welcome
    Martin_McCoyhas earned the badge Welcome
  • Welcome
    joywalimhas earned the badge Welcome
Show all badges
Terms & Conditions