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

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

collinae

Thank you so much @Paul Newcome, it worked perfectly for the quarterly!

Now I have a new issue, I have several calibrations that have yearly calibration due dates, that I would like also to have show as the end of the month. Currently, I am doing cal due date = last cal date + 365.

Paul Newcome

Try this for yearly:

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

collinae

Perfect! Thank you so much @Paul Newcome! I really appreciate all your help!

Paul Newcome

Happy to help. 👍️