Formulas

When using Array Join, if my price was 410.00 it removes the decimals on output and becomes 410 and it does not look good on my print sheet. How do I force a number to output two decimal places inside an ArrayJoin? Example, 410 becomes 410.00 Also, 0.1 becomes 0.10 etc Thanks! Joey

I am trying to figure the correct syntax for a consistent name concatenation. I have two fields, First Name, and Last Name. I have used the following formula to capitalize the first letter: UPPER(LEFT({First Name}))&LOWER(MID({First Name},2,LEN({First Name}))) & " " & UPPER(LEFT({Last Name}))&LOWER(MID({Last Name},2,LEN({Last Name}))) Which works great… Unless the last Name is TWO Names: First: “mary” Last: “sue beth” Becomes “Mary Sue beth” I need a way to check for two names and capitalize both, and also take account that some people may use a “-” between names. The goal is a formula that makes: “Mary Sue Beth”

I have four name fields that I wish to concatenate. First Name-1, First Name-2, Last Name-1 and Last Name-2. Objective: If First Name-2 and Last Name-2 two are both black, return First Name-1 Last Name-1 If First Name-2 is not blank and Last Name-2 is blank, return First Name-1 & First Name-2 Last Name-1 If both First Name-2 and Last Name-2 are not blank, return First Name-1 Last Name-1 & First Name-2 Last Name-2 I am new to Airtable. Thanks

I’m looking to automatically duplicate a row if the cell in a certain is ‘xxxx’. For example, if Column ‘B’ is ‘NO’, I want that row duplicated. Is this possible?

OK, hope someone can help here: I have a table names “ProdCo” - companies I’m following: In this table, I have a field named “Owner” - that links to a table - same name - where I put, among other things - email addresses: I now want to target certain companies for a sales initiative. I made a “check box” field in “ProdCo.” table to tag these companies. My question: How can I most efficiently generate a list of these Companies with their owners with their email addresses, I cannot for the life of me figger that out. Thanks in advance for any thoughts, Perry

Hi. maybe the free plan can not be integrated with Zapier or Integromat? I want to make a scenario on Integromat which takes action on my base free plan like delete a record and create a record, but for some reason, the actions do not take place.

Hi there, I’m trying to get a total number of years worked for my company, based on original Hire Date, Termination Date, Rehire Date, and Today(). I’ve been trying IF statements for a good day now, and while I’ve managed to get it to add up the years, it always rounds to the nearest whole number, even if it adds the decimals .00 after it. Not every employee has a termination date or a rehire date, but some do. I need the formula to determine whether fields are missing and calculate based on the information it has. IE: (today - rehire date) + (termination date - hire date) or if fields are missing, to calculate accordingly. This is what I have so far: =IF({Hire Date} = BLANK(), 0, IF({Term. Date} = BLANK(), DATETIME_DIFF(TODAY(),{Hire Date},'y'), IF(RehireDate = BLANK(), DATETIME_DIFF({Term. Date},{Hire Date},'Y'),SUM(DATETIME_DIFF(TODAY(),RehireDate,'Y'),DATETIME_DIFF({Term. Date},{Hire Date},'Y'))))) (I can’t figure out how to get the code

Hello there, I’m trying to write a formula that calculates the sum of values in another column. The tricky part is I want each row to add only the numbers above it in this other column, adjusting each time it goes down the row. For example: DATE EPISODES PRODUCED TODAY TOTAL EPISODES PRODUCED 7/11 2 2 7/12 3 5 7/13 1 6 Where the “TOTAL EPISODES PRODUCED” column is automatically tracking what’s being input into the “EPISODES PRODUCED TODAY” column.

I have a date field including M/DD/YYYY. I want to have a formula column that will use the date field to display just the name of the month in this format “01-January.” Being new to formulas I’m not sure how to do this. At first, I tried to identify the month using IS_BEFORE and IS_AFTER. That seemed cumbersome since it has to deal with the entire date. Then, I thought perhaps I could just identify the month within the string and repeat it with text, but I don’t know how to do that efficiently and couldn’t find a help thread that would explain parsing data that way. Any help would be appreciated.

I am brand new to airtable and have no experience of using this type (or any really) of database. I understand that there is a lot of assistance through the various guides but I made an error the other night which could have been costly had my database been up and running, rather than being tested by me. My error was, I thought I was deleting a cell, instead all of the records I had created disappeared. I’m sure that, in time, I will find the answers to my questions in the guides. However, time is not on my side if `i make the same error when the data is actually important. So, can you help? How do I not delete entire records by mistake? However, in the event that I did, surely there’s an “undo” button? Also, I’m sure that I read that your bases and their contents are automatically saved? How can I make back up bases in case I make my original mistake? Apologies for my questions, you may think I’m being lazy. However, I hope you understa

How would you write a date time formula for Beginning of current month to End of current month? The trick is, it’s the current month and the month always changes. Any Jedi out there?

I’m trying to track the dollar amount of a donor’s remaining pledge. Say they pledge 500, then donate 150, then donate 75. I want a formula that will calculate the remaining pledge as it goes down. Thanks!

Im not very good with formulas so I am looking for help. I log most of my business activity in an Airtable database so that at the end of the month I can invoice my customers. I found a formula here so that I can calculate and view weekly dollar value sales totals and it works great however there is a known quantity of work that I don’t log and I want to add that known amount to the calculated value displayed. To get the weekly sales value I have a Formula column with this calculation - DATETIME_FORMAT(Date,‘w’) and I have a view where I group by this column and it sums the amount column in my database.

I have a list of about 28 employees that I have created a scheduler for. I have their name, what team they are one, what shift they work and what work week we are currently on and other info. What I am wanting to do here is to have a formula that will automatically input their shift. Example: Jon works 8-5 M-F. So if I select him from my already existing record I would like his schedule to populate onto the selected work week and be added to the work calendar, then do this for all 28 employees so I am not manually entering that on Monday they worked 8-5, Tuesday 8-5, Wednesday 8-5 etc. Also is there a way for AirTable to recognize what week we are in and automatically populate that into the Grid I have created for their schedules? Any help is greatly appreciated!! Thank you all for your time.

My client just started using Airtable and she shared her airtable calendar with me, but the drag and drop feature for me to add attachments/documents to tasks is not available. Does she need to change something in her settings to grant me access to edit the tasks? There is also no option to manually add attachments Also, she is on mac and I am on PC - we both are running updated programs, but when she uploads .docs for me to download, some of the images (emojis) are not there. instead, its the box that indicates something was there but did not work with the formatting. I tried to provide screenshots for this post but it says images are not allowed. We don’t have this problem when we share documents on other platforms like word, dropbox etc.

i create one base that showing income -expenses-profit every thing is good now i have one problem, i create one field name is bank balance.i put formula in field that( total bank balance(30000) + Profit),problem is now this field ,every row doing - from 30000 i want every row should make - from last field row

Hi, I am interested in using a conditional formula to either automatically select from a single drop down or to use a phrase with emojis. {Program Status} In this field I want to indicate in one field if a program is: In Progress Complete Future {Program Start} and {Program End} or {Program Start and End} I have a field for start date and a field for end date (or I can create one field and type a range) I am looking for a formula that will automatically generate a {Program Status} based on today’s date. I would appreciate any help. Thank you!

Hi! I’m trying to use a formula or count to use in my base. I have in a board all the information for each of the employees and in another table I have the departments and I want to add how many women or men I have in each department. How can I count them with a formula?

Need a little help here :slightly_smiling_face: What i’m after is: If code is ER and Order Received is after 29th July 2019, value is 21 else value is 17.5. The order received field contains date and time like: 29 July 2019 11:01am IF(AND({Code}='ER',IS_AFTER({Order Received},"29 July 2019 12:00am"),21,17.5))

Is it possible to set something like this up and if so, how? If the answer in Column 1 is X, then display these multiple choice options for Column 2 If the answer in Column 1 is Y, then display these totally different multiple choice options for Column 2 I’m a brand new user, so please account for that in any answers. Thanks!

I’m using the following formula to check whether the data in another column (Call Forward) is before today. If yes, then enter today’s date, if no then enter the Call Forward date. IF(IS_BEFORE({Call Forward},TODAY()),TODAY(),{Call Forward}) I’m getting an error where the Call Forward field is blank which, I suspect, is caused by the blank (if blank I want today’s date). I’m guessing I need and OR statement to check for blanks but cannot find a suitable formula to check for blanks. I’ve tried using BLANK() but still getting an error: IF(OR(BLANK({Call Forward}),IS_BEFORE({Call Forward},TODAY())),TODAY(),{Call Forward}) Grateful for any advice.

Hey All, I have a formula below that checks if a rental is currently Active. I would like it to include bookings that are finishing today also as an “Active Rental :blue_car: ” ( and also bookings that start today). Is there a way to do that (or a better way to construct this formula? My formula is below; IF( IS_AFTER( {Date Started}, TODAY() ), “Upcoming :calendar: ”, IF( IS_AFTER( {Date Ending}, TODAY() ), “Active Rental :blue_car: ”, “Completed :white_check_mark: ” ) ) Thanks for any help. Cheers, Harry

I need a formula that will automatically fill in a draft date that comes 5 days before the due date AND has to land on a weekday. So far I have, IF({Project Size}=“Large”, DATEADD({Activity/Due Date},-5,‘d’)). This almost works for me but say I have a due date of 8/1 and I need the draft submitted for review no later than 7/26 (for a 5 workday review) how do I write the formula to land on that date and move dates according to due dates that follow?

Trying to get the “)” to display after the “X” in COMX (({SchDep} & “-”) & {Dest} & “-”) & {LdTo} & " (" & {SCAC} results in: 2200-KLO-KLO (COMX but (({SchDep} & “-”) & {Dest} & “-”) & {LdTo} & " (" & {SCAC} & “)”) results in a formula error. I’m also unable to display the date from a date field as part of the formula. It ends up looking like 2019-17-26-T00:00:00.000Z Also, is it possible to formulate two linked records {A} and {B} to display in one cell with the above?

Hi there, I am looking to setup an approval queue for our membership service, whereby applications are brought into the “Applicants” base, and when the dropdown selector for “Approval Status” is set to “Approved”, the record is added to our “Members” base, and deleted from Applicants base. Is this doable? Any idea how I could achieve this? Thanks so much! Adam