Skip to main content

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

  • March 19, 2018
  • 8 replies
  • 73 views
S

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

    8 replies

    E
    Forum|alt.badge.img+17

    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
      S
      • Sune_Theodorsen
      • Author
      • New Participant
      • March 21, 2018

      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!

      T
      Forum|alt.badge.img+4

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

        W
        Forum|alt.badge.img+1
        • Workshop_Team
        • New Participant
        • September 7, 2018

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

          M
          Forum|alt.badge.img+5
          • Mike_Johnston
          • New Participant
          • March 23, 2020

          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

          kuovonne
          Forum|alt.badge.img+29
          • kuovonne
          • Brainy
          • March 23, 2020

          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
)
            M
            Forum|alt.badge.img+5
            • Mike_Johnston
            • New Participant
            • March 25, 2020

            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!

            V
            Forum|alt.badge.img+16
            • Vernon_Fowler
            • Known Participant
            • December 14, 2020

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

            Terms & ConditionsAccessibility statement