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 - try this:

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'
      )
    )
  )  
) 

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

[JonathanBowen]

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:

IF(
  DATETIME_FORMAT({Due Date}, 'WW') < DATETIME_FORMAT(TODAY(), 'WW'),
  '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'
      )
    )
  )  
)

Which will show items as overdue only if they were due last week or earlier

[Holly_Nash]

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

[Holly_Nash]

Holly_Nash:

entires

entires *entries

Thanks,
Holly

[JonathanBowen]

Yes, makes sense. We just need to check the status first:

IF(
  Status != 'Published',
  IF(
    DATETIME_FORMAT({Due Date}, 'WW') < DATETIME_FORMAT(TODAY(), 'WW'),
    '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'
        )
      )
    )  
  )  
)

[JonathanBowen]

Oh, hang on, I see a logic error where the date is next year, gimme 5 mins

[Holly_Nash]

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

[JonathanBowen]

OK, so this is a better version of the first formula:

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'
        )
      )
    )  
  )
)

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).

[Holly_Nash]

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