Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,228 Replies
5939 Topics
F
FortGjort_TraniKnown Participant
asked in Formulas
$100 Reward - Calculate possible termination date of licenses

Hi! This is my table: https://airtable.com/shr0FiM4g1quNwnpG. It’s a table of our customers’ active licenses. Each license continue to be active until the customer calls us and terminates it. Then we remove the record from the table. Start day = The date the customer signed the license. Notice period (days) = The amount of days in which the customer need to terminate it’s license in advance. Otherwise the license is continued for another Agreement period. Agreement period (months) = The amount of months the customer is tied to it’s license. Tied until = The date the customer is tied to it’s license, I.e. Start day + Agreement period (months). The problem: I want the last field, Current possible termination date, to calculate a future date, that is the last day the customer is allowed to terminate it’s license. This date should be dynamic and based on today’s date and the criteria in the other fields. I’ve included some examples to show how it’s supposed to be calculated. Please let me

83
F
S
Sara_StahlbergKnown Participant
asked in Formulas
Formula Conditions

Is there a way for a formula to appear only on certain conditions? For example, I have the following situation: I have a # field and a date field I’d like to create a formula that auto calculates three-weeks out from my date field ONLY when the # field is not empty Here is the formula that I currently have: DATETIME_FORMAT(DATEADD({Clinic Date}, 3, ‘week’),“MM-DD-YYYY”) Is there a way to set up that additional condition so I don’t further clutter up my database? Thanks in advance to anyone who can assist!

54
J
A
Attila_NagyParticipating Frequently
asked in Formulas
Formula for specific digit length

Hello! I want to have a Formula as the Secondary Field. Now the Formula is: CONCATENATE(“PR-”,Autonumber,"/",YEAR(TODAY())) The “Autonumber” is a Field as Autonumber. How can I achieve, that the “Autonumber” in the CONCATENATE is always 4 digits long, independent of it’s length. Besides that I want only the two second digits of the year. So the values of the Autonumber is “4”, I want: “PR-0004/21” Thank you for your help in advance! Best regards Attila

649
A
P
Pierre1New Participant
asked in Formulas
Format '1' --> '001' Formula?

How can I display numbers with 3 digit all the time ? Like 1, 2, 3, 10, 125 → 001, 002, 003, 010, 125 Pierre,

393
P
A
Alyssa_MartinParticipating Frequently
posted in Formulas
Formula for a balance tracker / running balance

Hey AirTable brains trust, I’m trying to build a balance tracker in AirTable so that I can not only set up a budget, but also forecast what future account balances might be. I feel like I’m SO CLOSE. But I’ve found 2 flaws: the formula only seems to work if there’s an entry for the day before it. If there are no transactions for a few days, then the formula breaks somehow. the formula can’t cope if there are two transactions on the same day. I suspect the problem is found in the DateAmt column / formula (but that’s mostly an educated guess). I’ve straight copied the formula from the base that I’ve modelled it off (created by @W_Vann_Hall, of course). Here’s the base: Airtable 💰 Gold Price demo 2.0 - Airtable A quick demonstration of one method of implementing calculations based on relationships among records comparable to a spreadsheet's calculating based on subsequent rows. See [this forum... And here’s the formula: (VALUE(D

263
D
F
FortGjort_TraniKnown Participant
asked in Formulas
Value based on relation to date

Hi! I want a field to show one of three values (Active, Expires in a week or Expired) depending on if the field “Startdatum” is after today, within a week or before today. How do I solve this? Thanks!

113
F
B
Brian_BoydNew Participant
asked in Formulas
Remove duplicate in string and/or concatenate words

See the attached screenshot for an idea of what I’m trying to accomplish. I’m using a formula to concatenate the names of sellers and buyers. Simple except that if there are two or more names, how can I remove the identical last name, or add an & between names if they are different names?

382
B
B
Brian_BoydNew Participant
asked in Formulas
How to extract a word from a string?

I could use some assistance with extracting a word from a string. Here is an example of the string. 200 East Broadway #Z001 (Jacob to Seymore) This is the formula that almost has what I need but missing something. MID({Transaction Name}, SEARCH("#",{Transaction Name}), SEARCH("#",{Transaction Name}, SEARCH(" “,{Transaction Name})) - SEARCH(” ",{Transaction Name})) I’m trying to extract the apartment number (#Z001) from the string. Thanks in advance to anyone that tries to help with a solution.

214
B
H
Harrison_RiceNew Participant
posted in Formulas
Recurring tasks forTuesdays and Thursdays

Hello! I already use automations with dates and formulas to set recurring tasks at daily, weekly, and monthly intervals. I have a task that occurs twice a week (ideally on tues/thurs). Is there a way to do this and avoid AT scheduling tasks on weekends or irregular days (tues/thurs one week and mon/wed another etc.) Thanks!

41
Justin_Barrett
G
Graig_StettnerNew Participant
asked in Formulas
Looking for 55th birthdays in the current month

I have a list of people and their dates of birth. I’d like to know if that birthday falls in the current month. Right now, I have a column, the formula of which is IS_SAME({55th Birthday},TODAY()), which indicates if that birthday is today, but I’d like to see everyone in the list who turns 55 in the present month. So, in September, the September 55th birthdays are indicated; in October, the October 55th birthdays will be flagged; and so forth.

41
Justin_Barrett
J
Jeffrey_BrandtParticipating Frequently
posted in Formulas
Find overlap in Airtable

Hi, I have a base I use to request rooms. Is there a formula I can write to let me know if there is overlap in a request? For example: 1/10/2022: I have a request for 4 large conference rooms for Group 1 from 10-12 1/10/2022: I have a request for 3 medium conference rooms for Group 2 from 1-5 1/10/2022: I have a request for 2 large conference rooms for Group 3 from 9-11 I need a column that will show me that there is overlap with the request for Group 1 and 3, but that Group 2 has no overlap. Knowing which sessions have overlap, I can adjust the schedule, or room types, to ensure rooms are assigned correctly. Any suggestions? Thanks! Jeffrey

291
Justin_Barrett
B
ballgame168Inspiring
posted in Formulas
How to write an IF AND formula

I’m trying to create a formula where if two records are true it prints an answer. Here’s my formula: IF(AND({Role}= “Free”),{# Plans} => 5),“Cannot Add Plans”). I want it to show Cannot Add Plans if the Role = Free and the # of Plans => 5. It’s not working. Any help would be appreciated.

202
B
D
Daniel_BurkeInspiring
asked in Formulas
IF Field = "Name" then Calculate Various Hourly Rates from different table

Hi there! Trying to create a formula that pulls various hourly rate information from another table and calculates the hours x hourly rates into a grand total field. Additionally, rates vary depending on the employee selected, so once a name is selected, the formula will pull their respective rates from the other table and start the calculation. I believe this may require a roll up field as well, but not sure where that falls in the flow. Here’s a screen shot of the other table with various $$$ rates Here’s my main timesheet table where you’ll see a number of hour totals (Reg., OT, and 2xOT). Again, once I choose a name (People) I want to calculate those totals with their respective rates in the Total Compensation field. Really stuck on this one! Thanks!

202
D
N
Niko_Gomez1New Participant
posted in Formulas
Calculating net price based on Discount

Hello, I need help creating a formula that calculates the net price of a product based on the discounted % given to the unit Cost. Example Unit cost: 300$ Discount: 50% Net price: 150$ Column Names: Unit Cost ( original price of product ) Discount (discount % for this product) Net Unit Cost ( price of the product after the discount is applied) Any help would be greatly appreciated (: thank you!!

63
N
T
Torus_GlobalKnown Participant
asked in Formulas
How do I create this percentage formula?

Hey all, I’d love a hand with a formula if anyone knows this? I’d like to find what % a number is of another number, if that makes sense… So for example… If I have $500 in an investment account, and that investment is currently sitting at $750, what’s the formula to show the % increase (which equals to 50%)? The formula would of course show the opposite of -50% if the investment fell to $250. Any help is greatly appreciated. Thanks! Christopher

286
T
G
Gabriel_GiannetNew Participant
posted in Formulas
Determine if task has upcoming due date

Hi All, I am trying to figure out if a particular task has an upcming due date by seeing if a particular date field falls within 4 weeks from today. I have written the following formula: IF(AND({Milestone_Current_Due_Date} <= DATEADD(TODAY(), 1, ‘month’)),{Milestone Status}!=‘Completed’, “Upcoming Due Date”) But as you can tell from this screenshot it does not seem to operating correctly, saying some tasks have an upcming due date that is not within 4 weeks.

63
G
M
Mike_TulloNew Participant
posted in Formulas
Search in an array to count a specific string field

Hi everyone! I’m working in a pretty big AirTable right now and I have a question regarding searching an array. So if you see the first column I have a Lookup field with a large amount of items, some repeated, and I would like to search for each item and count how many times it was in the record. So for example, we would like to see how many “Mechanic” strings were in the array pulled from “Users (from User Categories)”. I thought the first step would be to convert the lookup field to an array so I have “Archetype Array”. However, I can not figure out how to search “Archetype Array” for specific string insides of it, like “Mechanic”. If you have any ideas to help, please let me know! Thanks!! :slightly_smiling_face:

623
Kamille_Parks11
Sean_Hyland1
Sean_Hyland1New Participant
posted in Formulas
Complicated reminder formula

Hi everybody, I need your help, please! I have a base with an action date, you have sixty days to complete the action. This is the deadline date. The deadline formula looks like this DATEADD({Action Date},60,‘days’) Now my problem is - If the deadline date is on a weekend, I want the reminder date to show as the next working day ie the Monday. Is there a formula that will accomplish this? Thanks in advance, Sean

75
Sean_Hyland1
A
Amanda_GronewolNew Participant
asked in Formulas
If statement with date - Error on blank cells

I am trying to check to see if the Date column is empty, then if not, display the date’s weekday in the Day of Week column. Here’s my formula in the Day of Week column: IF({Date}!=BLANK(),DATETIME_FORMAT({Date},'dddd'),"") I do not understand why I am getting errors for all the blanks. I’ve tried several different versions of the formula above. Any help appreciated.

231
A
D
Daniel_BurkeInspiring
asked in Formulas
Timesheets - Calculating total hours worked with empty fields

Hi there, having a difficult time writing a formula, for an employee timesheet. Currently, I am the using DATETIME_DIFF formula to calculate the total amount of hours worked within a 14-day period. I’m using two date/time fields for each day (ex: Day 1 - Start, Day 1 - End), so my formula is embarrassingly long: DATETIME_DIFF({Day 1 - End}, {Day 1 - Start}, ‘hours’)+DATETIME_DIFF({Day 2 - End}, {DAY 2 - Start},‘hours’)+DATETIME_DIFF({Day 3 - End}, {Day 3 - Start},‘hours’)… and so on 11 more times. My issue is, we have some employees that don’t work the full 14 days, so I need a formula that will calculate their total hours, and if some START/END fields are left blank, it will result as a “0” in the overall calculation. I am a novice when it comes to formula writing, and have just started using Airtable this Summer. Any help or guidance is appreciated. Thanks!

136
D
K
Kelly_SmyNew Participant
asked in Formulas
How to only add Start Date?

Hello, can someone help me figure out how to add ONLY the Start Date to a field? Currently, it is adding both the Start Date and Start Time. See images for my current formula. Thank you!

123
K
J
Jacob_HaltonNew Participant
asked in Formulas
I'm just trying to do a simple formula to multiply a field by 2 and it gives me an error

I’m trying to just double the value of a field in another field. I have one field called “costs” and another field called “goal” that I want to double to let me know how much profit I should try to make from an event. I’ve tried: {Total Costs} * 2 Also putting the 2 in parenthesis, leaving out the spaces, etc. Nothing seems to work.

246
J
S
Sparkn_AIParticipating Frequently
asked in Formulas
SUBSTITUTE Formula - Not removing apostrophe

Hey there, looking to create a simple slug and for some reason the apostrophe isn’t being removed. Example Title: Zoom Fatigue is real, so let’s deal with it. So make lowercase, remove apostrophes remove commas, remove full stops and replace spaces with dashes (etc) is what I’m after. LOWER( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( {Name}, "'","" ), ",", "" ), ".", "" ), " ", "-" ) ) I cannot fathom why the apostrophe is not being removed. I’m missing something easy… ideas?

152
S
J
Jennifer_CarmoNew Participant
posted in Formulas
Formula for Age only with Year of Birth

Hello all, I see a lot of answers for a formula to calculate the age with the DOB. but on my forms, I only ask people their Year of birth. Tried different formulas but can’t get one that works to calculate the age when I have available only the year of birth. Any advice? Thank you!

31
kuovonne
R
Richard_Eddy1Participating Frequently
posted in Formulas
IF(OR()) formula rejected

This formula is being accepted in a field, and is working: IF({NOTES}=“to June 2022”,“Yes”,“No”) but both of these more elaborate versions are being rejected: IF(OR({NOTES} = “to Dec 2021”, {NOTES} = “Life”, {NOTES} = “Prepaid 2022-2023”), ”Yes", “No”) IF( OR( {NOTES} = “to June 2022”, {NOTES} = “to Dec 2021” ), ”Yes”, “No” ) Can anyone enlighten me on what I am doing wrong please?

74
R

Badge winners

Show all badges
Terms & Conditions