Formula for organising week, month, year content

[Holly_Nash]

Hi all,

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?

Thanks for patience!!

[JonathanBowen]

Hi @Holly_Nash - this is a great place to start with formulas:

Airtable - Support

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

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:

Airtable - Support

Supported format specifiers for DATETIME_FORMAT

The DATETIME_FORMAT function will allow you to reformat the data from the date-type field into a string of your specifications. This is written in the form "DATETIME_FORMAT(Datetime, 'format s...

JB

[Holly_Nash]

So this I can understand:

IF(
AND(Status != ‘Published’, {Due Date}),
IF(
{Due Date} < TODAY(),
‘Overdue!!’,

I can completely understand and see the logic of the above (I found this https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference#date_and_time_funct... and https://support.airtable.com/hc/en-us/articles/216141218 article for guidance of denoting stuff.

But the rest I can’t comprehend, specifically parts like this:
IF(
DATETIME_FORMAT({Due Date}, ‘WW’) = DATETIME_FORMAT**(TODAY(), ‘WW’),
‘This week’,

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!

[Holly_Nash]

Thank you! As you say completely overwhelmed when I found these articles. Sorry I replied before you did :grinning_face_with_big_eyes:

[JonathanBowen]

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

[Holly_Nash]

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

[Holly_Nash]

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 :slightly_smiling_face:

[JonathanBowen]

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(
  AND(Status != 'Published', {Due Date}),
  IF(
    {Due Date} < TODAY(),
    'Overdue!!',
    IF(
      DATETIME_FORMAT({Due Date}, 'WW') = DATETIME_FORMAT(TODAY(), 'WW'),
      'This week',
      IF(
        DATETIME_FORMAT({Due Date}, 'MM') = DATETIME_FORMAT(TODAY(), 'MM'),
        'This month',
        IF(
          DATETIME_FORMAT({Due Date}, 'YYYY') = DATETIME_FORMAT(TODAY(), 'YYYY'),
          'This year',
          'Something else'
        )
      )
    )  
  ),
  ''
)

(Notice the '' in the last but one line)

So, in full, the formula is saying:

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

[Holly_Nash]

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

[Zwibbl_TV]

Do you know how to check if the date is today?
{Due Date} = TODAY() doesn’t seem to work…

[Jeremy_Oglesby]

@Zwibbl_TV

Try this:

Airtable - Support

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...