I love the visual usability of Airtable, but am restricted by a total lack of understanding formulas (or Excel!). I’ve read all the Airtable guides, but can’t figure out my specific request and the formula needed, or whether it’s a formula I need or something else!
Apologies if stupid question, but:
I’ve made a table of content for social media and other media, listing content title, type, platform to post on, due date. Currently, it looks like this:
Name . Content Type. Status . Due Date .
Gifs for Facebook . Creative Idea 31/8/2019
(Imagine details filled out further, I can’t link to or insert a screenshot)
I am trying to work out a way to group these under three groups - either ‘This Week’, ‘This Month’ or ‘This Year’. I’d want the entries to be automatically populated into these groups dependent on the due date I manually enter. Is there a formula for this, where should I add it/format?
Note that it won’t show “this month” right now as the end of August is also the end of the week, but when you get into Sept a future date in Sept will show this month!
I’ve added “Overdue” for past events and “Something else” for any other date, i.e. not overdue, this week, this month or this year. You can edit the text of this if you want.
JB
Also note that something that is overdue could still be in this week, e.g. yesterday, not sure if this is the behaviour you want, so you could also try:
Which will show items as overdue only if they were due last week or earlier
This is amazing thank you!
I have applied the formula to the primary field (Name) and it’s working. My only issue is that as you mentioned, entires whose due date has passed are listed as Overdue, when i have in fact published some. I determine this by the Status field (selecting either ‘Idea’, ‘Published’, ‘To Create’). Is there a formula to incorporate that status and column to dictate whether Overdue comes up or not (Say for example, I’ve ‘Published’ so ‘Overdue’ does not come up?) or maybe this is all a little too confusing.
It may need a whole rethinking of my database. I’m not at all minded for this stuff!
Best,
Holly
This is amazing thank you!
I have applied the formula to the primary field (Name) and it’s working. My only issue is that as you mentioned, entires whose due date has passed are listed as Overdue, when i have in fact published some. I determine this by the Status field (selecting either ‘Idea’, ‘Published’, ‘To Create’). Is there a formula to incorporate that status and column to dictate whether Overdue comes up or not (Say for example, I’ve ‘Published’ so ‘Overdue’ does not come up?) or maybe this is all a little too confusing.
It may need a whole rethinking of my database. I’m not at all minded for this stuff!
Best,
Holly
entires *entries
Thanks,
Holly
This is amazing thank you!
I have applied the formula to the primary field (Name) and it’s working. My only issue is that as you mentioned, entires whose due date has passed are listed as Overdue, when i have in fact published some. I determine this by the Status field (selecting either ‘Idea’, ‘Published’, ‘To Create’). Is there a formula to incorporate that status and column to dictate whether Overdue comes up or not (Say for example, I’ve ‘Published’ so ‘Overdue’ does not come up?) or maybe this is all a little too confusing.
It may need a whole rethinking of my database. I’m not at all minded for this stuff!
Best,
Holly
Yes, makes sense. We just need to check the status first:
How you understood my comment, so impressive thank you.
It works, the field is left blank if status is published. I also followed your example and created a separate field for the formula (titled Schedule) rather than applying to the already populated Name field, which I did first of all).
My only final issue is that it reports #ERROR! in the formula field for entries without a date, rather than ‘Something else’ as the formula asks for?
Thanks so much, genuinely,
Holly
OK, so this is a better version of the first formula:
This says “If the content isn’t published AND has a due date, then evaluate as per the rules, otherwise, leave blank”.
So:
published would be blank
no due date would be blank
The “something else” should probably read “future” or “next year” or something like that (although of course, the date could be many years into the future in theory).
OK, so this is a better version of the first formula:
This says “If the content isn’t published AND has a due date, then evaluate as per the rules, otherwise, leave blank”.
So:
published would be blank
no due date would be blank
The “something else” should probably read “future” or “next year” or something like that (although of course, the date could be many years into the future in theory).
Fantastic, perfect.
I’m trying to work out the main bit you actually changed to implement, particularly with changing the year formula logic?
What do you recommend for an absolute beginner like me to try and wizz up on this (rather than bothering you!)? I have minimal excel experience and I’m not exaggerating when I say I’m pretty sure I’m discalculate and find logic overwhelming (more of a creative/writer)?
I can’t even find info on the formula attributes you’ve used, such a MM to somehow know you mean this month?
Thank you so much!
Holly
Hi @Holly_Nash - this is a great place to start with formulas:
If you’re really new to this, even the above documentation can be a bit overwhelming. As an example, this:
IF(logical, value1, value2)
in my view, doesn’t really tell the beginner how to set up an IF statement (what does “logical” mean??). But the pattern or formula “template” makes a lot more sense alongside as example:
IF(Date = BLANK(), "Please enter date", "Date entered")
Like any skill, you have to put in a certain amount of hours to get the hang of it, so that would be another piece of advice. Nested IF statements, like the above, are probably at the more complicated end of the continuum, so don’t be worried if you don’t get the hang of these straight away. And…do post your queries back to the community - there are always people ready to jump in with an answer or two.
The ‘MM’, ‘WW’ thing is a bit buried in the formula guide but you can find it here:
I see how Due Date is included, but not where/how the parts I’ve italicised make sure that if today is this week, it populised ‘This Week’ Mainly, can’t understand what the first instance of WW is doing!
Hi @Holly_Nash - this is a great place to start with formulas:
If you’re really new to this, even the above documentation can be a bit overwhelming. As an example, this:
IF(logical, value1, value2)
in my view, doesn’t really tell the beginner how to set up an IF statement (what does “logical” mean??). But the pattern or formula “template” makes a lot more sense alongside as example:
IF(Date = BLANK(), "Please enter date", "Date entered")
Like any skill, you have to put in a certain amount of hours to get the hang of it, so that would be another piece of advice. Nested IF statements, like the above, are probably at the more complicated end of the continuum, so don’t be worried if you don’t get the hang of these straight away. And…do post your queries back to the community - there are always people ready to jump in with an answer or two.
The ‘MM’, ‘WW’ thing is a bit buried in the formula guide but you can find it here:
JB
Thank you! As you say completely overwhelmed when I found these articles. Sorry I replied before you did :grinning_face_with_big_eyes:
I see how Due Date is included, but not where/how the parts I’ve italicised make sure that if today is this week, it populised ‘This Week’ Mainly, can’t understand what the first instance of WW is doing!
This is saying:
If the WW value (week number) of due date = the WW value of “today” then evaluate to “this week”
See my other answer for the date formatters
JB
This is saying:
If the WW value (week number) of due date = the WW value of “today” then evaluate to “this week”
See my other answer for the date formatters
JB
Thank you! I’ll keep playing around and asking questions, really helpful. And makes me feel better Nested Formulas are at the harder end!
Best,
Holly
One last thing if you spot this - what is ‘something else’ now doing in my formula?
I’m trying to understand how it populates blank as opposed to ‘something else’ or whatever I change that text to
The working formula above misses out the last argument (in this case, the bit that gives ‘blank’) as this is optional in an IF statement. So the “full” formula would be:
IF (status is not published AND there is a due date)
then evaluate as per the rules
otherwise blank ('')
So the ‘something else’ only comes into play if the record is not published AND has a due date AND is not one of this week, this month, this year.
And you will get blank ('') if either the record is published OR it doesn’t have a due date (or both)
Make sense?
JB
The working formula above misses out the last argument (in this case, the bit that gives ‘blank’) as this is optional in an IF statement. So the “full” formula would be:
IF (status is not published AND there is a due date)
then evaluate as per the rules
otherwise blank ('')
So the ‘something else’ only comes into play if the record is not published AND has a due date AND is not one of this week, this month, this year.
And you will get blank ('') if either the record is published OR it doesn’t have a due date (or both)
Make sense?
JB
Perfect. I’m going to read through this all weekly to try get to grips with it.
Hopefully, eventually, there’ll be a bank of tutorials and explanations like yours as guidance. As you pointed out, the Airtable guides assume some understanding of particular formula terms etc.
Thanks for your time today!
Holly
The working formula above misses out the last argument (in this case, the bit that gives ‘blank’) as this is optional in an IF statement. So the “full” formula would be:
