Last Day of Month / End of Month / EOMONTH

#1

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"
)
0 Likes

#2

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

2 Likes

#3

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.

1 Like

#4

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.

1 Like

#5

DATEADD(DATETIME_FORMAT(DATETIME_FORMAT(TODAY(),‘YYYY’)&’-’&DATETIME_FORMAT(TODAY(),‘MM’)+1&’-01’,‘YYYY-MM-DD’),-1,‘day’) - resulted in: 12/30/2000

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. :-:smile:

0 Likes

#6

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'
)
2 Likes

#7

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.

0 Likes

#8

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