# Last Day of Month / End of Month / EOMONTH

Topic Labels: Formulas
Solved
11078 17
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

If I want to specify the last day of the current month in a cell as a date, what is the syntax? In Google Sheets & MS Excel it is EOMONTH (End Of Month).

EDIT - HERE IS THE FORMULA - THANKS KAMILLE & JUSTIN

``````DATETIME_FORMAT(
DATETIME_PARSE(
DATETIME_FORMAT(
TODAY(),
'YYYY'
) & '-' &
(VALUE(
DATETIME_FORMAT(
TODAY(),
'MM'
)
)
+ 1)
& '-01',
'YYYY-MM-DD'
) , -1, 'day'
)
, "MM-DD-YY"
)``````
1 Solution

Accepted Solutions
4 - Data Explorer

When using this formula Airtable does not seem to read this as a ‘date’ making it difficult to use the End of Month date in other formulas.

I found using the below formula to be easier and enables the use of the date in other formulas, such as findind how many days left until the end of the month.

If my start date is ‘01/01/2019’ then the formula will result in ‘31/01/2019’ and will pick this up in date format.

If you are wanting the start of a month, then I use this formula:

17 Replies 17
16 - Uranus

There is probable a more elegant way to do this, but you can try this formula:
`DATETIME_FORMAT(DATEADD(DATETIME_FORMAT(DATETIME_FORMAT(TODAY(),'YYYY')&'-'&DATETIME_FORMAT(TODAY(),'MM')+1&'-01','YYYY-MM-DD'),-1,'day'),'DD')`

5 - Automation Enthusiast

Thank You Kamille. This provided the number 31. Which technically speaking is what I asked. I will tweak this code to return the EOMONTH as a date. So, rather than 31, it will return 03-31-2019. Thanks for taking the time to put together that formula, it’s much appreciated.

Also - note to @Airtable, please consider adding EOMONTH functionality. As you can see it is quite complicated to write this as a function.

16 - Uranus

If you remove the outer date time format portion of the formula you should get your results as a full date as opposed to just the day.

5 - Automation Enthusiast

DATETIME_FORMAT(DATETIME_FORMAT(TODAY(),‘YYYY’)&’-’&DATETIME_FORMAT(TODAY(),‘MM’)+1&’-01’,‘YYYY-MM-DD’) - resulted in 2001-01-01

So, I am not sure that we are on the same page. :- :grinning_face_with_smiling_eyes:

18 - Pluto

I messed with the formula a bit, and was able to get it working.

``````DATEADD(
DATETIME_PARSE(
DATETIME_FORMAT(
TODAY(),
'YYYY'
) & '-' &
(VALUE(
DATETIME_FORMAT(
TODAY(),
'MM'
)
)
+ 1)
& '-01',
'YYYY-MM-DD'
) , -1, 'day'
)``````
5 - Automation Enthusiast

Thank You Justin. For me your formula returned 3/30/2019, which is close enough for what I am trying to do.

I still recommend to AirTable to place End of Month into the date options. Thanks Again Justin.

Sorry. I overlooked the formatting you’d requested. That’s easily addressed by wrapping DATETIME_FORMAT around the above. Here’s the full formula with that addition:

``````DATETIME_FORMAT(
DATETIME_PARSE(
DATETIME_FORMAT(
TODAY(),
'YYYY'
) & '-' &
(VALUE(
DATETIME_FORMAT(
TODAY(),
'MM'
)
)
+ 1)
& '-01',
'YYYY-MM-DD'
) , -1, 'day'
)
, "MM-DD-YY"
)``````
4 - Data Explorer

When using this formula Airtable does not seem to read this as a ‘date’ making it difficult to use the End of Month date in other formulas.

I found using the below formula to be easier and enables the use of the date in other formulas, such as findind how many days left until the end of the month.

If my start date is ‘01/01/2019’ then the formula will result in ‘31/01/2019’ and will pick this up in date format.

If you are wanting the start of a month, then I use this formula:
`DATEADD(DATEADD(DATETIME_FORMAT({Date in Question},'YYYY-MM-01'),1,'month'),-1,'day')`