# EOMONTH similar

✭✭✭

I am trying to automate the Month/Year field to add one from the pervious cell. Excel formula: =EOMONTH(E13,1)
Is there any work around in smartsheet?

• ✭✭✭✭✭

Something like below would potentially work for that?

=((MONTH(DATE(2024, 7, 1)) + 1) + " / " + ((YEAR(DATE(2024, 7, 1))

If you found this comment useful, please let me know by clicking one of the buttons below: Awesome, Insightful, Upvote, or Accepted Answer. Your feedback will assist others looking for the same information and also help me out.

Thanks!

Nick Stafford

• ✭✭✭✭✭

You could always just nested IF statement the crap out of it, like so:

=if(E13=1,2,if(e13=2,3,if(e13=3,4 ….etc all the way until you get if(E13=12,1,"") as the final formula

Additionally there is a MONTH function that will take the month from any date and denote it by number (1, 2, 3, 4). This formula could be helpful in this siutation as well.

If you want more specific advise, please post some screenshots and ID the columns you are using :)

If you found this comment useful, please let me know by clicking one of the buttons below: Awesome, Insightful, Upvote, or Accepted Answer. Your feedback will assist others looking for the same information and also help me out.

Thanks!

Nick Stafford

• Smartsheet doesn't have EOMONTH but there is a way.
1. Have a starting date field. I'm going to use a "Starting Date" column in this example
2. Use this formula looking at your starting date field.
=IF([starting date]row# + 1 = "", "", DATE(YEAR([starting date]row#), MONTH([starting date]row#) + 1, 1) - 1)

• Remember to update the "row#" with the proper row.
• This formula takes the previous date field and adds one month to it, setting the day to the end of that month (by subtracting 1 from the first day of the next month).

Drag the formula to apply to different rows or columns.

Steve Mercer

Project Manager and Smartsheet Consultant

https://www.pathtosummit.com/

• ✭✭✭✭✭

Are you trying to just add one to the month or year or are you trying to add an entire date plus 30 days? There is a MONTH function that will take the month from a date and then become the number Jan = 1, Feb = 2, etc…. The application of this would be dependent on your specific use case. If you include som screenshots, I would be happy to help with an exact formula

If you found this comment useful, please let me know by clicking one of the buttons below: Awesome, Insightful, Upvote, or Accepted Answer. Your feedback will assist others looking for the same information and also help me out.

Thanks!

Nick Stafford

• ✭✭✭

I would like to automate the 05, 06, 07, etc based on the starting date cell of 4/1/2023

• ✭✭✭✭✭

Try this!

05 Cell = MONTH([Reference Date}1) +1

06 Cell = MONTH([Reference Date}1) +2

07 Cell = MONTH([Reference Date}1) +3

If you found this comment useful, please let me know by clicking one of the buttons below: Awesome, Insightful, Upvote, or Accepted Answer. Your feedback will assist others looking for the same information and also help me out.

Thanks!

Nick Stafford

• ✭✭✭

It gives Invalid Data Type error

• ✭✭✭✭✭
edited 08/28/24

What is your column type for the column with dates in it? Maybe try and change it to Date and then make it not restricted to dates only so you can still hav text there.. does this help?

If you found this comment useful, please let me know by clicking one of the buttons below: Awesome, Insightful, Upvote, or Accepted Answer. Your feedback will assist others looking for the same information and also help me out.

Thanks!

Nick Stafford

• ✭✭✭

Date Expected Error

• ✭✭✭✭✭

I will remove the need to reference that column

05 Cell = MONTH(DATE(2023,04,01)) +1

06 Cell = MONTH(DATE(2023,04,01) +2

07 Cell = MONTH(DATE(2023,04,01) +3

etc…

If you found this comment useful, please let me know by clicking one of the buttons below: Awesome, Insightful, Upvote, or Accepted Answer. Your feedback will assist others looking for the same information and also help me out.

Thanks!

Nick Stafford

• ✭✭✭

That gave me the month Number (ie 5, 6, 7, etc). Is it possible to add year behind it (/2024)? Similar to

=((MONTH(DATE(2024, 7, 1)) + 1),"/"2024)

• ✭✭✭✭✭

Something like below would potentially work for that?

=((MONTH(DATE(2024, 7, 1)) + 1) + " / " + ((YEAR(DATE(2024, 7, 1))

If you found this comment useful, please let me know by clicking one of the buttons below: Awesome, Insightful, Upvote, or Accepted Answer. Your feedback will assist others looking for the same information and also help me out.

Thanks!

Nick Stafford

• ✭✭✭

That works and I am set!! Thank you very much!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!