Skip to main content

I have a base to manage my vendor payments, and a table that warehouses the vendors’ contracts and payment terms. In that contract table, I’d like to make a formula field that returns the next date when I should expect an invoice, depending on the payment terms (in this table, that is a single-select field titled “Invoice Schedule”).

 

One of the “Invoice Schedule” options is “monthly,” meaning the formula field I am trying to make should just return the last day of the month (preferably month-over-month, as several contracts are long term).

 

Here’s how I’ve set up that part of the formula - AT accepts the formula, but returns only blank values. I think that I’m misusing or misunderstanding SWITCH() - wondering if anybody can advise on what I’m doing incorrectly here? Also definitely feels like I’ve overcomplicated this solution but was the best I could figure out, so super open to more elegant solves if you’ve got them!

 

IF(
  {Invoice Schedule} = "monthly",
  SWITCH(
    MONTH(TODAY()),
    "1", DATETIME_FORMAT(DATETIME_PARSE("1/31/" & YEAR(TODAY())), "MM/DD/YY"),
    "2", DATETIME_FORMAT(DATETIME_PARSE("2/28/" & YEAR(TODAY())), "MM/DD/YY"),
    "3", DATETIME_FORMAT(DATETIME_PARSE("3/31/" & YEAR(TODAY())), "MM/DD/YY"),
    "4", DATETIME_FORMAT(DATETIME_PARSE("4/30/" & YEAR(TODAY())), "MM/DD/YY"),
    "5", DATETIME_FORMAT(DATETIME_PARSE("5/31/" & YEAR(TODAY())), "MM/DD/YY"),
    "6", DATETIME_FORMAT(DATETIME_PARSE("6/30/" & YEAR(TODAY())), "MM/DD/YY"),
    "7", DATETIME_FORMAT(DATETIME_PARSE("7/31/" & YEAR(TODAY())), "MM/DD/YY"),
    "8", DATETIME_FORMAT(DATETIME_PARSE("8/31/" & YEAR(TODAY())), "MM/DD/YY"),
    "9", DATETIME_FORMAT(DATETIME_PARSE("9/30/" & YEAR(TODAY())), "MM/DD/YY"),
    "10", DATETIME_FORMAT(DATETIME_PARSE("10/31/" & YEAR(TODAY())), "MM/DD/YY"),
    "11", DATETIME_FORMAT(DATETIME_PARSE("11/30/" & YEAR(TODAY())), "MM/DD/YY"),
    "12", DATETIME_FORMAT(DATETIME_PARSE("12/31/" & YEAR(TODAY())), "MM/DD/YY")
  )
)

 

There are probably many different ways of returning the last day of today’s month,  but here is one way to do it. Maybe someone can come up with an even shorter formula than this! :)

DATEADD(

DATETIME_PARSE(

MONTH(DATEADD(TODAY(),1,'month')) & "-" & 1 & "-" & YEAR(DATEADD(TODAY(),1,'month')),

'M-D-YYYY'),

-1, 'day')

Hope this helps! If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Try this:

IF(
  {Invoice Schedule} = "monthly",
  DATEADD(
    DATEADD(
      DATETIME_PARSE(
        DATETIME_FORMAT(TODAY(), 'YYYY-MM') & '-01'
      ), 
      1,
      'months'
    ),
    -1,
    'days'
  )
)

I'm curious what other invoice schedules you have though. If you have stuff like quarterly or like bi-monthly, you would need the latest payment date, right?  If so, maybe this might be useful:

SWITCH(
  {Invoice schedule},
  'Monthly',
  DATEADD(
    DATEADD(
      DATETIME_PARSE(
        DATETIME_FORMAT({Latest payment date}, 'YYYY-MM') & '-01'
      ), 
      2,
      'months'
    ),
    -1,
    'days'
  ),
  'Quarterly',
  DATEADD(
    DATEADD(
      DATETIME_PARSE(
        DATETIME_FORMAT({Latest payment date}, 'YYYY-MM') & '-01'
      ), 
      4,
      'months'
    ),
    -1,
    'days'
  )
)

 

Appreciate these solutions, thank you! For my base and our other invoicing conditions we won’t need that additional level of complexity, although I think this is a great resource for other models.

 

For my original problem, seems like my mistake was in using double quotations around my DATETIME_FORMAT notation instead of single, so it looks like it’s working now!

I like this topic :
To be honest, after I reviewed question, I have doubt what exactly needed. Anyway, that’s end of term based on Date field.

DATEADD(
  DATETIME_FORMAT(
    DATEADD(Date,1+
    IF({Invoice schedule}='Quarterly',MOD(12-MONTH(Date),3))
    ,'M')
  ,'YYYY-MM-01'),
-1,'d')

 

 

By the way, I think the upper SWITCH formula by ​@Josh_Colina is not working despite looking well and correct, because MONTH function output is number. So, when it compare 6 with “6” it thinks “Not equal”. And therefore cannot find any answer. The solution is to change “1”, ...  to  1,…..
“2”, ...  to  2,….. etc…. hmmm, I would better suggest “lazy solution” - to convert MONTH to string by 
“” & MONTH(TODAY())

Reply


Terms & Conditions