End of Month referencing date column

As I have combed through the solution for this with the smartsheet community resources, I however am still having issues populating the last day of the month when referencing a date column. @Paul Newcome seems like the expert in this. Halp.


Best Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @lgonzales,

    A trick for this is to find the 1st date of the next month and subtract a day from it using a formula like below:

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

    Hope this helps! 😊

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Make sure the column you are putting the formula in is set as a date type column.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!