Formulas

Hello! I'm new to Airtable formulas & could use some much needed guidance! I need to calculate the total with an option of a discount if a multi-select answer is chosen. For context, this is in reference to grades of elementary curriculum books purchased. I am trying to create a formula that will show me:If Rural Discount multi-select = 'Small school (one classroom per grade level)' answer, then total # of grades licensed x $250 + additional invoice amount= total invoice costIf Rural Discount multi-select does NOT equal 'Small school (one classroom per grade level)' answer, then total # of grades licensed x $500 + additional invoice amount= total invoice costHere was what my closest effort was: Additional info:

I have a team that wants to send a weekly digest that shows updates only from the previous week. I found a formula that can show if a date is this week or not but can't figure out a way to show just dates from the previous week 

Currently I have a Date & Time (duration) field.  I have combined them into  The combined date & time formula is showing a different time zone it seems?  I want it to be Los Angeles.  Is there a way to do this?"COMBINED START DATE & TIME" FORMULA:IF( ({All Day Event}), DATETIME_FORMAT({Start Date & Time}, "MM/DD/YYYY"), DATETIME_FORMAT({Start Date & Time}, "MM/DD/YYYY HH:mm") ) 

Hi all, i need help form you 🙂i have 2 tables : - VariantTable -CostTableThe key is the SKU, this field is in the 2 Tables.I would like to add colunn in VarianTable with the CostPrice.How to create field with Automaticaly put the CostPrice when SKU is same.  Thanks for you help

If I add an attachment of an Image via DropBox, is there any way to see, use a formula, or API, to pull the actual DropBox URL instead of the Airtable URL which of course expires?

I have several single select columns with "yes/no" selection answers. I want to make a sort of if/than formula to combine the information from those yes/no columns. For example, in the photo below, there are columns a, b, c ,d, e. We will call those each "foods" for the purpose of the example. If someone says "Yes" to foods a, d, e but they say No to foods b and c, can I make a column that combines the yes data? I would like a formula field to show that the person has chosen foods a, d, e, just like how a multi select field looks. I have thought about using If statements, but I'm not sure if that will allow multiple answers into 1 column. I'm not even 100% sure a formula can give me the information I need. Does anyone have any input on this? 

Hi there, I have a repetitive formula field that I need to turn into a lookup / roll-up field in another table.Here is the formula field for reference. What are the steps I need to take to allow for this formula field to be a lookup field on another table?IF(AND(WEEKDAY({Go Live Date / Event Start Date (from Program Name)})=1, {Email Week}='Week 1'),DATEADD({Go Live Date / Event Start Date (from Program Name)},-25,'Days'),IF(AND(WEEKDAY({Go Live Date / Event Start Date (from Program Name)})=1, {Email Week}='Week 2'),DATEADD({Go Live Date / Event Start Date (from Program Name)},-18,'Days'),IF(AND(WEEKDAY({Go Live Date / Event Start

Hello,how can I calculate the weighted average? Example:[-4, 0, 2, 0] -> I get this values via a roolupI want the result to be -2 i.g.. Correct me please if I am wrong.I'm open to use multiple formulas. Best regardsJohannes 

We have a Start Date (Date field) and a Start Time (Duration Field).  I want to combine them together.The first formula I have does combine them, but not in the way that airtable calendar view recognizes it as a date/time field.Can you help me create a formula that will format it  properly (more like the "copy" field below, but showing the correct time. Currently I am using:DATETIME_FORMAT({Start Date},'M/D/YYYY')&' '&CONCATENATE(FLOOR({Start Time} / 3600)

Hi all,Just asking for a kick in the right direction for a formula, extension/script and/or automation that'll put the PriceInput value from my Price List table into the Database1 table (whether it's displayed in the Lookup or thrown into the PriceOutput field) when the "Agency, Board Type, Size, Type" match.I can see the option's available to select the suitable record via manually selecting it in the linked cell (as pictured in attachment) but this is obviously the part I need automated when a new record gets put in or updated.Database1 : https://airtable.com/apprcSs2FuTW9JKPJ/shrX6iv9CyKfOFLN3Price List : https://airtable.com/apprcSs2FuTW9JKPJ/shrMo95QtDPC7n3HqGreatly appreciated in advance 🙏

Small charity here thank you for reading!  I am trying to make a sign up sheet for volunteers to sign up to wrap Christmas presents for children - that bits sorted.  But I need to sort parking for them. On the main base theres a linked column that pulls up there name and there car registrations so it pulls through for each hour which volunteers are in and in each hour slot it tells me what the number plates are.I am trying to hide those fields (again fine) and have a field for each day that shows all car number plates expected in the car park and ideally remove the ones that are there for multiple slots and just show them the once.Am I being silly what would I use to find this I swear I used to be able to do show it as a roll up or something but for the life of me I cannot work it out. Thanks in advance 

Is it possible to exclude records that have a zero value from an average rollup?

Hi!Please excuse my lack of knowledge as I am a novice user of Airtable, and also not familiar with databases.I am seeking guidance and help on a small matter.I have collated birthdays and anniversaries of my friends, about 900+. I have created the base and the Table and called it 'Master View'. Now in this table, I want to have a 'Daily View' wherein based on the date on a daily basis, I get to see the sifted data of only those who are celebrating their birthday and anniversary on that day. I can then share the link of this 'View' with all my friends for a better coordination amongst us on daily basis.Please help. Thanking in advance.(The fields in the 'Master View' of my Table are Name, Name of Spouse, Date of Birth, Date of Marriage, Contact Number, and Mugshot (photo) of the person.)

I am trying to combine two fields into one. Both fields have a date and I am getting an error message when I try to combine them with & or Concatenate formula.  DATETIME_FORMAT({Date PL NT}, 'l') & " " & DATETIME_FORMAT({Date of PL Local Orders}, 'l') HELP!  

Hi all,I want to use a formula to group my outgoing payments by week. The tricky part is that I want to start the week on Thursday (so it runs Thursday to Wednesday). Can anyone help me with a formula for that please? TIA, H

Hello! Thanks in advance to everyone.I'm wondering how can I create a formula which allows me to know if an event has ocurred the past week, but I don't want the event of the past 7 days, I really need the past week. For example: if today is wednesday I want to know what had happened between the monday and the sunday of the past week.I have done it by this formula: IF(DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'Europe/Madrid'), 'W')-1={EVENT WEEK}, 1, 0), but the problem with this formula is that it is not going to work the first week of the year, because, obviously, that is the 1st week and the last week is 54 so the formula

I have two data tables;Table 1 has records with "recommended zip codes" these zip codes are in a single delimited fieldTable 2 has a data table with zip codes and corresponding populationsUltimately I just want to find the total population for all zip codes listed in the single field (even within an interface)I can see this being solved in two different ways:a formula to recognize each separate value stored in the same fieldsome way to separate all the separate zip code values into distinct columns within the same recordAny ideas on how to solve for either of these? 

Hi all! I am back looking for support with a formula to return a date based on a "created" date and a variable.So far I've been trying to get an IF statements with a DATETIME_FORMAT included to work, but I'm making no progress...My Goal: Create a column that shows an Estimated Pickup Date which is either 11 days after the created date, or 6 days after the created date if the "rush" checkbox is ticked.This is what my columns look like:Any brilliant minds out there who can point me in the right direction?Thanks in advance!Devon

Hi All! I am trying to work with the DATETIME_DIFF formula but running into some issues. I am assuming the issues come from time/timezone complications, but not sure what the most elegant way around it is...My goal: I am trying to calculate the difference in time between an expected completion date and an actual completion date.The situation: Both date fields included time stamps driven by API connected apps and are set to the same time zone.Date 1: Estimated Pickup Date (defaults to 12am)Date 2: Ready for Pickup Timestamp (an exact time a checkbox was ticked, i.e. modified)Formula I am currently trying: IF({Ready for Pickup Timestamp}, DATETIME_DIFF({Ready for Pickup Timestamp},{Estimated Pickup Date}, "days"))

I have a product that expires in one year. If they purchase an upgrade, it extends the expiration another year. The records are added individually to Airtable. How do I say IF this email address has product A, check to see if it also has product B in another record?

Hello! I am trying to figure out a formula to help me calculate a score for a multiple select field. For example, I have 6 different issues to choose from. If they select “no issues” I want the score to be “20”, if they select 1 of the issues, I want the score to be “5”, and if they select 2 or more of the issues, I want the score to be “0”. Is there a way to do this with a multiple select field? Thank you in advance!

Hi Folks, I've been trying to come up with an elegant solution to this and am hoping the community here can help me. I have several fields of checkboxes marking different assets available in our library. I would love to create a signal field that would list which fields are still unchecked. Is this possible? I've tried a few variations but nothing is working. Thanks!

Hi - I am trying to build an IF(OR statement that returns a value if any of the fields are blank. The way it's currently written returns COMPLETE even though some fields are blank.Here is what I have so far: IF(OR({Premiere Project File - RBs}=BLANK(),{Delivery instructions}=BLANK(),{Final Viewing Link}=BLANK(),TRT=BLANK(),"INCOMPLETE"),"COMPLETE") 

Hello,I have been looking at the boards for hours now and can't seem to find the formula I am looking for. I have it in Excel however, the formula does not work in airtable. =DATEDIF(0,C2,"Y") &" Years "&DATEDIF(0,C2,"YM")&" Months "&DATEDIF(0,C2,"MD")&" Days "Ideally, I would like to take out the Total Days part and just get the Breakdown from the Start Date. At this point though, either way would work. Can someone please help me out with the formula. 

I hope you're all well. I have a question to ask you, I would like to create a progress bar that summarises all my subtask using checkboxes. Basically, each time I check all the checkboxes in my subtask, the progress bar shows me 100%.Is this possible? Can anyone help me? I'm looking for something like this :