Get number of days in a month (eg. 31 for March)


#1

Hi,
I’m trying to make a formula that gives me the last day of the current month. So, while we’re in March I want it to return 31 or 2018-03-31 and when we get to April return 30 or 2018-04-30.
It seems pretty simple in JS (https://stackoverflow.com/questions/1184334/get-number-days-in-a-specified-month-using-javascript) - but don’t know how to do it on Airtable.

Any ideas?
Thanks a lot!

Best
Sune


#2

Try this formula:

DATETIME_DIFF(DATETIME_PARSE("1-"&CONCATENATE(MONTH(TODAY())+1)&"-"&YEAR(TODAY()),"D-M-YYYY"), DATETIME_PARSE("1-"&MONTH(TODAY())&"-"&YEAR(TODAY()),"D-M-YYYY"),"d")

You can explore it here: https://airtable.com/shrXEpPCnjcgCm76n

  • You could pass a Date instead of using TODAY()
  • I had to use CONCATENATE() to convert the Next Month to a string

#3

Works perfectly - thanks a lot Elias!


#4

Couldn’t you just have a 12 row table and look it up?


#5

Much after the topic, but for those who come here looking for an answer this is simple and would work too:
DAY(DATEADD(DATEADD(DATETIME_FORMAT(TODAY(),‘YYYY-MM’),1,‘month’),-1,‘days’))