EOMONTH similar

S_Ellis

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?

NickStaffordPM

Something like below would potentially work for that?

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

NickStaffordPM

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 :)

SteveAtPathtoSummit

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.

NickStaffordPM

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

S_Ellis

https://community.smartsheet.com/discussion/comment/451308#Comment_451308

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

NickStaffordPM

Try this!

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

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

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

S_Ellis

https://community.smartsheet.com/discussion/comment/451498#Comment_451498

It gives Invalid Data Type error

NickStaffordPM

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?

S_Ellis

https://community.smartsheet.com/discussion/comment/451503#Comment_451503

Date Expected Error

NickStaffordPM

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…

S_Ellis

https://community.smartsheet.com/discussion/comment/451589#Comment_451589

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)

NickStaffordPM

Something like below would potentially work for that?

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

S_Ellis

https://community.smartsheet.com/discussion/comment/451596#Comment_451596

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