Did a date formula change?
=DATE(YEAR([Last Month]@row) - 1, MONTH([Last Month]@row), 1)
=DATE(YEAR([Current Month]@row), MONTH([Current Month]@row) - 1, 1)
I have been using this above formula for the last 6+ months in 20+ sheets but I noticed it is not working anymore. Did something change or do I need to update something since the year changed?
Best Answer
-
@MichelleBohn I believe this will get you there. should only need it in the "last month" column
=DATE(YEAR([Current Month]@row), if(MONTH([Current Month]@row) - 1 = 0, 12 ,MONTH([Current Month]@row) - 1), 1)
Answers
-
@MichelleBohn since the month of January is 1 you cannot subtract 1 and have it know that you mean December. You have to write an if state to account that if month = 1, return 12.
What your formula returns is date(2023,0,1) which is causing the errors.
-
Thank you so much Samuel, this makes sense now, I appreciate it!
-
@MichelleBohn happy to help. Let me know if you are having trouble with the IF formula.
-
Samuel, I thought I had it but if you don't mind can you assist with the IF formula I would greatly appriciate it. Thank you
-
@MichelleBohn I believe this will get you there. should only need it in the "last month" column
=DATE(YEAR([Current Month]@row), if(MONTH([Current Month]@row) - 1 = 0, 12 ,MONTH([Current Month]@row) - 1), 1)
-
Thanks again for the speedy responses. This worked only issue is that it is reading the current year so it puts it at 12/1/23 instead of 12/1/22 as last month. I'm going to manually update for now, thanks again for all the help. I was putting the If statement at the end which is why it wasn't working right for me.
-
@MichelleB Oh you're right, here
=DATE(if(MONTH([Current Month]@row) - 1 = 0, YEAR([Current Month]@row)-1,YEAR([Current Month]@row)) , if(MONTH([Current Month]@row) - 1 = 0, 12 ,MONTH([Current Month]@row) - 1), 1)
-
You're a lifesaver! You are incredible with formulas. I have to use google and watch youtube all the time to figure out formulas. Thank you this worked perfectly.
-
Happy to help! 😀
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!