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?
Best Answer
-
Something like below would potentially work for that?
=((MONTH(DATE(2024, 7, 1)) + 1) + " / " + ((YEAR(DATE(2024, 7, 1))
Answers
-
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 :)
-
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.
-
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
-
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
-
It gives Invalid Data Type error
-
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?
-
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…
-
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))
-
That works and I am set!! Thank you very much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 422 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 143 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
