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

1845 0
cancel
Showing results for
Did you mean:
4 - Data Explorer

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 8
13 - Mars

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
4 - Data Explorer

Works perfectly - thanks a lot Elias!

7 - App Architect

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

4 - Data Explorer

Much after the topic, but for those who come here looking for an answer this is simple and would work too:

5 - Automation Enthusiast

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

18 - Pluto

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

``````DAY(
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
)

``````
5 - Automation Enthusiast

Nice work @kuovonne. Thanks!

7 - App Architect

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: