Get first & last date of a month from a date in another column

Gavin Seaton · 02/01/23

Im trying to get 2 formula's that will calculate the first & last dates of a month based on a date in another column on the same row.

So for instance if 'Date of Movement' column has 10/1/2023 i would like the 'Start Date of Month' column to return 1/1/2023.

Likewise if 'Date of Movement' column has 10/1/2023 i would like the 'End Date of Month' column to return 31/1/2023.

I know in excel the first formula would be: =EOMONTH([@[Date of Movement],-1)+1

And the second formula: =EOMONTH([@[Date of Movement]],0)

However i cant find a solution in Smartsheet that does similar.

Gavin

Nick Korna · 02/01/23

For start of the month:

=DATE(YEAR([Date of Movement]@row), MONTH([Date of Movement]@row), 1)

For end of the month:

=IFERROR(DATE(YEAR([Date of Movement]@row), MONTH([Date of Movement]@row) + 1, 1), DATE(YEAR([Date of Movement]@row) + 1, 1, 1)) - 1

Hope this helps; any questions etc. then just ask! 😊

Nick Korna · 02/01/23