Automate End Month Date

Heshy Tauber

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.

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!