Formula for organising week, month, year content

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

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

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

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

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

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

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:

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

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

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:

JB

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_functions 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!

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

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

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

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

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

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

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

@Zwibbl_TV

Try this: