Automate End Month Date

Hi ,

I have started a Sheet to manage tasks for of the payroll Firm's I run

I need to get into 1 cell automatically last date of the month so if its February its the 28th if its Jan should be automatically the 31st

Which formula can I set up that it should automatically be set for end date of the month? so i can get set up notifications on that date to Process payroll

Thank you

Heshy.

Find more posts tagged with

Formulas

Email Notifications

Account and User Management

Comments

Mike Wilday

Try this formula.

=IF(MONTH(Date2) < 12, DATE(YEAR(Date2), MONTH(Date2) + 1, 1) - 1, DATE(YEAR(Date2), 12, 31))

Found it here: https://community.smartsheet.com/discussion/how-get-eom-end-month

Michelle Watkins

Hi Heshy,

I would also recommend turning on the project settings and tapping into the duration an predecessors tied to the start date / end date columns.

https://help.smartsheet.com/articles/765737-project-sheet-columns-start-date-end-date-duration-complete-and-predecessors

J. Craig Williams

FYI

If Dependencies are on, the two project dates can not have formulas.

Craig

Michelle Watkins

Thank you, Craig, for clarifying for the group. I apologize I missed noting this

I wanted to just recommend another option tapping into the project setting to drive the dates.

J. Craig Williams

No prob.

I figured you and Mike knew that, the comment was for Heshy's benefit.

Craig

Mike Wilday

Yep. The formula I shared for Heshy was assuming he wanted an additional date column for setting up reminders and notifications based on a calculation of the end of the month.

Thanks for adding these additional thoughts!

Heshy, please let us know if we've met your needs or have any additional questions!