Formula for last day in the month
Answers

Hello
Could you please help me identify the correct formula for last day in the month.

There is not a function to find the last day in the month but you can write a formula using the DATE function, to calculate it. Because months have different lengths (28, 29, 30, 31 days), we need to find the start date of the subsequent month (which is always 1) and subtract on day to find the end date.
For example, if you want to know the last day in the month based on a date in a cell called "Base Date" (formated as Date Type) your formula would be
= DATE(YEAR([Base Date]@row), MONTH([Base Date]@row) + 1, 1)  1)
If this formula is placed in another Date formatted column on the same row, it will return the last date of the month that Base Date is in.
It does it by creating a date:
 The year of which it takes from the Base Date cell. Let's say this was Jan 17th 2024. The year would be 2024.
 The month it also takes from the Base Date, and it adds 1 to it. So for Jan 17th the month in the new date is February.
 The day is always 1, i.e. Feb 1st.
It then subtracts one from this date, and that gives us the last day in January.
There is a slight complication in that you can't subtract 1 from the month of January to find December. 11 is not 12. So we add another little IF to return December 31st of the year in the Base Date cell, if the month is December.
= IF(MONTH([Base Date]@row) = 12, DATE(YEAR([Base Date]@row), 12, 31), DATE(YEAR([Base Date]@row), MONTH([Base Date]@row) + 1, 1)  1))
You don't need to output these dates into columns, you can use them within formula. You also don't need to have a start date cell, you could use TODAY() to find the end date of the current month.
I hope this helps.
I noticed you had a couple of questions here that were not answered  I think they are being missed as they will not appear in the "unanswered questions" list. When asking questions in the community, please mark helpful answers as accepted and start new threads for new questions. This will prevent your subsequent questions from being missed (they look like followups rather than new questions) and help other users with similar questions to find answers.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!