Last Day of Month / End of Month / EOMONTH

[Jon_Frampton]

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(
    DATEADD(
        DATETIME_PARSE(
            DATETIME_FORMAT(
                TODAY(),
                'YYYY'
            ) & '-' &
            (VALUE(
                DATETIME_FORMAT(
                    TODAY(),
                    'MM'
                )
            )
             + 1)
             & '-01',
            'YYYY-MM-DD'
        ) , -1, 'day'
    )
    , "MM-DD-YY"
)

[paul_smith1]

this post was helpful

[

[Diego_Bermudez]

Great help, it works like magic. Thanks!

[Jason_Samuels]

This was really helpful!

[Patrick_Bliss]

I realize this is kind of a dead thread, but wanted to share the solution I came up with. Requires no reference fields and is pretty short and elegant (I think!). Basically subtracts today’s day number from today’s date and adds a month, returning this month’s last day:

DATEADD((DATEADD(TODAY(), -DAY(TODAY()), ‘days’)), 1, ‘months’)

[Julian_E_Post]

I’m working on a tutorial for how to recreate an EOMONTH function and this is a brilliant way to simplify it, @Kamille_Parks . I love @Patrick_Bliss 's too, although if I wasn’t the creator and stumbled across both formulas, I think it would be easier to figure out what’s going on with the DATETIME_FORMAT way of doing it.

I had no idea that you can enter numbers into a DATETIME_FORMAT function, which is really nice and useful. Having a hard time wrapping my brain around why it works. Like, when you sub in ‘01’ for ‘DD’, Airtable doesn’t actually know that you want to replace ‘DD’, because you didn’t specify that anywhere… so it’s just making an educated guess?

[Kamille_Parks]

I actually think its ignoring the “01”. I have since learned that DATETIME_FORMAT({Date in Question},'YYYY-MM') always resolves to the first of that month. Similarly, “YYYY” would resolve to Jan 1 of that year.

So the shortest formula you could write using my method would be

DATEADD(DATEADD(DATETIME_FORMAT({Date in Question},'YYYY-MM'),1,'month'),-1,'day')

Patrick’s solution works as well. I prefer DT Format because often times people need to adjust for timezone, which you can only do if you’re using the DT Format function anyway.

[Julian_E_Post]

That makes sense! Thanks :slightly_smiling_face:

[Irit_Levi]

Kamille_Parks:

DATEADD(DATEADD(DATETIME_FORMAT({Date in Question},'YYYY-MM'),1,'month'),-1,'day')

This is the perfect solution. Thanks @Kamille_Parks