How to formulate a cell to display the end of the month?

collinae

I am very new to Smartsheet and am trialing it for work to manage our equipment files so we can get automated notifications for due dates. Currently, I am having difficulty formulating cells to give me an end of the month due date.

For example, we have semi-annual calibrations due that we need to complete before the end of the month. I need a way that when I insert the last completed cal date, I get a date returned to the end of that month as the next due date.

In my example below, I have the cal due date = last cal date + 180 (~6 months)

As you can see this displays the due date as 9/11/22. However, in reality we can complete this calibration up to 9/30/22.

Any help would be much appreciated, thank you very much!

Paul Newcome

Try this instead:

=IFERROR(DATE(YEAR([Last Cal Date]@row), MONTH([Last Cal Date]@row) + 7, 1), DATE(YEAR([Last Cal Date]@row) + 1, MONTH([Last Cal Date]@row) - 5, 1)) - 1