Skip to main content

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

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

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


Works perfectly - thanks a lot Elias!

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

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

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


Hi @Elias_Gomez_Sainz, thanks for your formula! Does the formula work with dates in the month of December? I can get it to work with all other months except when I pass it a Dec date. Curious if others are seeing it work with Dec dates. Thanks

Workshop_Team’s formula works well, but you have to convert the curly quotes to straight quotes:


DAY(

  DATEADD(

    DATEADD(

      DATETIME_FORMAT(YEAR({date}) & "-" & MONTH({date}) ,"YYYY-MM"),

      1,

      "months"

    ),

    -1,

    "days"

  )

)

Or you could try this formula:


SWITCH(MONTH({date}), 

  1, 31,

  2,  IF(MOD(YEAR({date}), 400) = 0,

        29,

        IF(MOD(YEAR({date}), 100) = 0,

          28,

          IF(MOD(YEAR({date}), 4) = 0,

            29,

            28

          )

        )

      ),

  3, 31,

  4, 30,

  5, 31,

  6, 30,

  7, 31,

  8, 31,

  9, 30,

  10, 31,

  11, 30,

  12, 31

)

Workshop_Team’s formula works well, but you have to convert the curly quotes to straight quotes:


DAY(

  DATEADD(

    DATEADD(

      DATETIME_FORMAT(YEAR({date}) & "-" & MONTH({date}) ,"YYYY-MM"),

      1,

      "months"

    ),

    -1,

    "days"

  )

)

Or you could try this formula:


SWITCH(MONTH({date}), 

  1, 31,

  2,  IF(MOD(YEAR({date}), 400) = 0,

        29,

        IF(MOD(YEAR({date}), 100) = 0,

          28,

          IF(MOD(YEAR({date}), 4) = 0,

            29,

            28

          )

        )

      ),

  3, 31,

  4, 30,

  5, 31,

  6, 30,

  7, 31,

  8, 31,

  9, 30,

  10, 31,

  11, 30,

  12, 31

)

Nice work @kuovonne. Thanks!

Workshop_Team’s formula works well, but you have to convert the curly quotes to straight quotes:


DAY(

  DATEADD(

    DATEADD(

      DATETIME_FORMAT(YEAR({date}) & "-" & MONTH({date}) ,"YYYY-MM"),

      1,

      "months"

    ),

    -1,

    "days"

  )

)

Or you could try this formula:


SWITCH(MONTH({date}), 

  1, 31,

  2,  IF(MOD(YEAR({date}), 400) = 0,

        29,

        IF(MOD(YEAR({date}), 100) = 0,

          28,

          IF(MOD(YEAR({date}), 4) = 0,

            29,

            28

          )

        )

      ),

  3, 31,

  4, 30,

  5, 31,

  6, 30,

  7, 31,

  8, 31,

  9, 30,

  10, 31,

  11, 30,

  12, 31

)

Thank you @kuovonne - the switch function version works in December, each leap year, and is clear and easy to read and understand. 🙂 😀

Reply


Terms & Conditions