This was incredibly helpful! I used to feel pretty comfortable with formulas when I was using Excel, but Airtable is a whole new ball-game for the formula function. Thanks for sharing your process in such detail!
Gareth...Thank you for all the videos you post on Airtable and Zapier. They are very helpful in learning how to build a more user friendly database for my company. AWESOME SAUCE!!!
Agreed - I'd love to see a better interface that makes formulas easier to understand as you write them. Even for experienced formula writers, the existing interface is clunky and difficult to use. Fingers crossed!
Hey Gareth, Your videos have been so helpful! I'm a young entrepreneur who recently took a project management job (in which i had no experience) with a very new company and ive been able to help them grow and establish processes all thanks to you! One question...for the end date and time in this example, is there something that can be dropped into the existing formula for start date and time to add the duration or is it an entirely different formula? If its different I'll give it my best shot, but I would love to see a video on that as well. Once again wanted to say thanks for all of your help, I know this is an older video but hopefully you see this!
I love your videos. This one had me shaking my head, had to watch it a few times. I would love to see a version of this for a formula for project dates/deadlines. ei event date, 3 weeks before, one week before, etc.
Hey Patti - thanks for watching/commenting! Ya, these formulas can get complicated quickly, eh? I'll definitely add a new idea to our content calendar for building a video with date formulas. Thanks for the recommendation!
This demo is really just a "DEMO", and it is demonstrating a 100% useful method/process to get to an end goal. It's absolutely useful (And sometimes necessary) to have IFs inside of IFs inside of IFs, and all that great stuff, but it becomes a serious nightmare to maintain. In *MY* case, I've been writing software since I was 8 (Started with the Vic20) and my mind got an itch that there had to be an easier way to do this, so I went to work. The shortcut I came up with: DATETIME_PARSE(CONCATENATE({Date}," ",{Times}),"YYYY-M-D h:mma") Make sure that your formula column is set to use "Use the same time zone (GMT) for all collaborators" under the Formatting tab. NOTE: Because this video was made a year ago, and because I just started digging into AirTable literally this morning (At 3am), this function may not have existed when this video was made. But it works today!
Thank you this just solved a problem I have been trying to fix with my formula returning exact minutes in his nested IF statement. So much easier! Thank you
Thanks for this video, Very informative. Can you help with extending this to include how to update the time with not just the hours but also the minutes. I’m new this and am struggling how to update the time with minute ie 12:15. Any help would be greatly appreciated. Thanks
Hey man , thank for your sharing. Any ideia how i get the Formula see a result from a Column at X Days behind , to give a result based on the result frm X days behind Like IF ( " Day" -1 - "NUM" is 1 AND "DAY" -2 is not 0 , " Strong , " Weak" ) AND ect....
excelent video! Just a question, let's say I have fifteen columns in a row with numbers and i want to extract only the 6 highest values and sumarize it into another column. What would be the formula then? Thank you so much!
Hi Paige, thanks for watching! Can you tell me more about the output you are expecting when you say you're looking for a different sum? Are you looking for a date output? If so, I think you could use the DATEADD() formula to add a number of days or weeks to a date, if it passed through your Nested IF.
Thank you for the detailed explanation, it was helpful to me. Could you please tell if Airtable can define the value type with a formula? E.g. I have two values: "No data" and "0" which have different meanings in my table and "No data" shouldn't be counted. But the formula defines "No data" text like zero and counts it accordingly. Is there a way to avoid this behavior?
Hi, and thanks for watching! Yes, in place of "No Data" try to use BLANK(). That should tell Airtable that the field is empty, and your only data type would then be a number. Hope this helps!
The manipulation of date and time values would be much easier if Airtable treated formulas and values in the same way as Excel does. Where dates and times are stored as numeric values and the display is formatted. On the cut/paste work in combining parts of functions, I see this as something that will work. However with fairly complex formulas doing this makes the resulting formulas difficult to read and verify.
Airtable formats currency fields with a thousands-place comma separator. Is there a way to format an integer to have the comma separator for the thousands place? I especially need a way to do this within a formula in the same way you can wrap a date field in a DATETIME_FORMAT to get the exact display format you want. I read one conversation thread in the Airtable community forum, but I could not figure it out.
I am new to airtable and I am trying to add a formula that will return a 2 year date. For instance a CPR certificate will expire in two years from said date. I want to know what the date will be in two years. Can you do a video that shows just that?
I have one more question. If the date passes that I am setting I want that field to change color to notify me. what more to the formula would I need Thank you in advance
Assigning a color to a record is possible, but it isn't achieved inside a formula field. Instead you'd want to assign a conditional color to your view. For example, you might say, "change the color of a record if the date is within 7 days of today." Check out my recent video on adding colors to records: ruclips.net/video/7Z3f-HbjwmE/видео.html Hope this helps!
So if I have a list of prices that fluctuate over time, can I make a formula for a separate column that tells me the change in each number? EX. January sales were 10,000, February sales were 9,500 and March sales were 11,125. And I want to know the change from Jan -> Feb and Feb -> Mar in a column by itself?
This is a tough one to explain in a message, but I'll give it a shot. When you write a formula, it applies to the entire field (column). In this way, it's very different from Excel. In Excel, I'd write a formula like: =sumif( {Sales} , {Month} , "January" ) - this would sum all the sales for the month of January (assuming these were columns in Excel. But Airtable formulas apply to the entire field and they don't have a SUMIF formula. However, this is a feature of Airtable, not a limitation. To take advantage of the feature, you can link your SALES table to a TOTALS table through a linked record. You can link each sale to a corresponding month/year (i.e. SEPT-2018) to easily see the totals of that month in the totals table. Then in the TOTALS table, you'll want to add a ROLLUP field with the SUM(VALUES) function to bring in the totals from your SALES table. Alternatively, you can explore the blocks for this - the pivot block and the graph block in particular are great for this type of high level analysis. For more on those ROLLUPs and how to use them, check out this vid: ruclips.net/video/ksFiYFfO8vM/видео.html
Hey Seth - not sure exactly what you're trying to do here. Do you want to label the date that a box was checked? Or do you have a date in another field that you want to bring in when you check the box? If it's the first one: IF( {CHECK FIELD} , LAST_MODIFIED_TIME() ) If it's the second one: IF( {CHECK FIELD} , {DATE FIELD} ) Where {CHECK FIELD} and {DATE FIELD} are the fields where you store the corresponding data.
What if i would want to make the time for example 15:25 to 19:20, what should i change in that formula. as it currently just takes the first 2 integers
Hey Kuba, great question. This would require a nested formula (a formula inside a formula). If "Date" is the field where you have the original date/time, your formula would look like this: DATEADD(DATEADD(Date,3,'hours'),55,'minutes') Basically, this first adds 3 hours to the "Date" field. Then it adds an additional 55 minutes. Hope this helps!
Getting the same result in Excel is so much quicker and easier. Why can't airtable get all formula functionality like excel? It would have been super hit.
Join us for our *FREE LIVE TRAINING* that teaches the building blocks of automation: www.garethpronovost.com/webinar-registration
This was incredibly helpful! I used to feel pretty comfortable with formulas when I was using Excel, but Airtable is a whole new ball-game for the formula function. Thanks for sharing your process in such detail!
Thanks for watching, Christy. Glad to know it helped! 😁
Gareth...Thank you for all the videos you post on Airtable and Zapier. They are very helpful in learning how to build a more user friendly database for my company. AWESOME SAUCE!!!
Glad you like them!
Thanks for this. It’s really helpful. I really hope Airtable improves on making the formula function simpler.
Agreed - I'd love to see a better interface that makes formulas easier to understand as you write them. Even for experienced formula writers, the existing interface is clunky and difficult to use. Fingers crossed!
Hey Gareth, Your videos have been so helpful! I'm a young entrepreneur who recently took a project management job (in which i had no experience) with a very new company and ive been able to help them grow and establish processes all thanks to you! One question...for the end date and time in this example, is there something that can be dropped into the existing formula for start date and time to add the duration or is it an entirely different formula? If its different I'll give it my best shot, but I would love to see a video on that as well. Once again wanted to say thanks for all of your help, I know this is an older video but hopefully you see this!
I love your videos. This one had me shaking my head, had to watch it a few times. I would love to see a version of this for a formula for project dates/deadlines. ei event date, 3 weeks before, one week before, etc.
Nevermind, I figured it out! I was adding spaces in the formula. Took them out and voila!
Hey Patti - thanks for watching/commenting!
Ya, these formulas can get complicated quickly, eh? I'll definitely add a new idea to our content calendar for building a video with date formulas. Thanks for the recommendation!
This demo is really just a "DEMO", and it is demonstrating a 100% useful method/process to get to an end goal. It's absolutely useful (And sometimes necessary) to have IFs inside of IFs inside of IFs, and all that great stuff, but it becomes a serious nightmare to maintain. In *MY* case, I've been writing software since I was 8 (Started with the Vic20) and my mind got an itch that there had to be an easier way to do this, so I went to work.
The shortcut I came up with: DATETIME_PARSE(CONCATENATE({Date}," ",{Times}),"YYYY-M-D h:mma")
Make sure that your formula column is set to use "Use the same time zone (GMT) for all collaborators" under the Formatting tab.
NOTE: Because this video was made a year ago, and because I just started digging into AirTable literally this morning (At 3am), this function may not have existed when this video was made. But it works today!
Thank you this just solved a problem I have been trying to fix with my formula returning exact minutes in his nested IF statement. So much easier! Thank you
Thanks for this video, Very informative. Can you help with extending this to include how to update the time with not just the hours but also the minutes. I’m new this and am struggling how to update the time with minute ie 12:15. Any help would be greatly appreciated. Thanks
Hey man , thank for your sharing. Any ideia how i get the Formula see a result from a Column at X Days behind , to give a result based on the result frm X days behind
Like
IF ( " Day" -1 - "NUM" is 1 AND "DAY" -2 is not 0 , " Strong , " Weak" ) AND ect....
Thank you for all the Videos.
Thanks for watching!
excelent video! Just a question, let's say I have fifteen columns in a row with numbers and i want to extract only the 6 highest values and sumarize it into another column. What would be the formula then? Thank you so much!
I have to practice this my man lol...Thank you.
Oh thanks Gareth this was great... Can you do a video using nested If statements using date fields as variables to produce a different sum?
Hi Paige, thanks for watching!
Can you tell me more about the output you are expecting when you say you're looking for a different sum? Are you looking for a date output?
If so, I think you could use the DATEADD() formula to add a number of days or weeks to a date, if it passed through your Nested IF.
Thank you for the detailed explanation, it was helpful to me. Could you please tell if Airtable can define the value type with a formula? E.g. I have two values: "No data" and "0" which have different meanings in my table and "No data" shouldn't be counted. But the formula defines "No data" text like zero and counts it accordingly. Is there a way to avoid this behavior?
Hi, and thanks for watching! Yes, in place of "No Data" try to use BLANK(). That should tell Airtable that the field is empty, and your only data type would then be a number. Hope this helps!
@@GarethPronovost Thanks for the quick answer. I'll try it!
The manipulation of date and time values would be much easier if Airtable treated formulas and values in the same way as Excel does. Where dates and times are stored as numeric values and the display is formatted.
On the cut/paste work in combining parts of functions, I see this as something that will work. However with fairly complex formulas doing this makes the resulting formulas difficult to read and verify.
12:40 why not just use the 24hr format?
I'm strugling to integrate airtable to google data studio. Do you know how to do it?
i actually was wondering how to do this. Lol wow that's pretty complicated.
Not the easiest thing to do, but worth the effort!
Airtable formats currency fields with a thousands-place comma separator. Is there a way to format an integer to have the comma separator for the thousands place? I especially need a way to do this within a formula in the same way you can wrap a date field in a DATETIME_FORMAT to get the exact display format you want. I read one conversation thread in the Airtable community forum, but I could not figure it out.
I just want column C to say what percentage column A is out of column B.... can someone help
I am new to airtable and I am trying to add a formula that will return a 2 year date. For instance a CPR certificate will expire in two years from said date. I want to know what the date will be in two years. Can you do a video that shows just that?
Hey Belinda - thanks for the question. Try this formula out and let me know if you have any questions:
DATEADD({YOUR FIELD HERE},2,'years')
Thanks!
THANK YOU!!!!! it worked
Awesome! :D
I have one more question. If the date passes that I am setting I want that field to change color to notify me. what more to the formula would I need Thank you in advance
Assigning a color to a record is possible, but it isn't achieved inside a formula field. Instead you'd want to assign a conditional color to your view.
For example, you might say, "change the color of a record if the date is within 7 days of today."
Check out my recent video on adding colors to records: ruclips.net/video/7Z3f-HbjwmE/видео.html
Hope this helps!
So if I have a list of prices that fluctuate over time, can I make a formula for a separate column that tells me the change in each number? EX. January sales were 10,000, February sales were 9,500 and March sales were 11,125. And I want to know the change from Jan -> Feb and Feb -> Mar in a column by itself?
This is a tough one to explain in a message, but I'll give it a shot.
When you write a formula, it applies to the entire field (column). In this way, it's very different from Excel. In Excel, I'd write a formula like: =sumif( {Sales} , {Month} , "January" ) - this would sum all the sales for the month of January (assuming these were columns in Excel.
But Airtable formulas apply to the entire field and they don't have a SUMIF formula. However, this is a feature of Airtable, not a limitation.
To take advantage of the feature, you can link your SALES table to a TOTALS table through a linked record. You can link each sale to a corresponding month/year (i.e. SEPT-2018) to easily see the totals of that month in the totals table. Then in the TOTALS table, you'll want to add a ROLLUP field with the SUM(VALUES) function to bring in the totals from your SALES table.
Alternatively, you can explore the blocks for this - the pivot block and the graph block in particular are great for this type of high level analysis.
For more on those ROLLUPs and how to use them, check out this vid: ruclips.net/video/ksFiYFfO8vM/видео.html
@@GarethPronovost Thanks! That's really helpful! I appreciate you taking the time to reply!
@@stephensmith3208 Happy to help!
How do I write an if checked what is the date?
Hey Seth - not sure exactly what you're trying to do here. Do you want to label the date that a box was checked? Or do you have a date in another field that you want to bring in when you check the box?
If it's the first one:
IF( {CHECK FIELD} , LAST_MODIFIED_TIME() )
If it's the second one:
IF( {CHECK FIELD} , {DATE FIELD} )
Where {CHECK FIELD} and {DATE FIELD} are the fields where you store the corresponding data.
What if i would want to make the time for example 15:25 to 19:20, what should i change in that formula. as it currently just takes the first 2 integers
Hey Kuba, great question. This would require a nested formula (a formula inside a formula). If "Date" is the field where you have the original date/time, your formula would look like this:
DATEADD(DATEADD(Date,3,'hours'),55,'minutes')
Basically, this first adds 3 hours to the "Date" field. Then it adds an additional 55 minutes.
Hope this helps!
Getting the same result in Excel is so much quicker and easier.
Why can't airtable get all formula functionality like excel? It would have been super hit.
I actually im finding parallel formula for sumifs of ms excel just sort perticular customer sale sum in one row