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.