How To Extract Month And Year Only From A Date and if the date column is blank Return Blank?
Hi All,
I have to generate a monthwise report to show how many Requests are in a particular month I tried the following formula. I have also added IF(ISBLANK()""), so that it leaves the cell blank if the date is not there in the [Request Received Date] column. One it is not considering the Year and well as throwing #INVALID OPERATION error.
=IF(ISBLANK([Request Received Date]@row), "", IF(MONTH([Request Received Date]@row) = 1, "January", IF(MONTH([Request Received Date]@row) = 2, "February", IF(MONTH([Request Received Date]@row) = 3, "March", IF(MONTH([Request Received Date]@row) = 4, "April", IF(MONTH([Request Received Date]@row) = 5, "May", IF(MONTH([Request Received Date]@row) = 6, "June", IF(MONTH([Request Received Date]@row) = 7, "July", IF(MONTH([Request Received Date]@row) = 8, "August", IF(MONTH([Request Received Date]@row) = 9, "September", IF(MONTH([Request Received Date]@row) = 10, "October", IF(MONTH([Request Received Date]@row) = 11, "November", IF(MONTH([Request Received Date]@row) = 12, "December")))))))))))))
Please Help!
Best Answer
-
Hi Paul,
It is resolved I used following
=IFERROR(IF(MONTH([Request Received Date]@row) = 1, "January", IF(MONTH([Request Received Date]@row) = 2, "February", IF(MONTH([Request Received Date]@row) = 3, "March", IF(MONTH([Request Received Date]@row) = 4, "April", IF(MONTH([Request Received Date]@row) = 5, "May", IF(MONTH([Request Received Date]@row) = 6, "June", IF(MONTH([Request Received Date]@row) = 7, "July", IF(MONTH([Request Received Date]@row) = 8, "August", IF(MONTH([Request Received Date]@row) = 9, "September", IF(MONTH([Request Received Date]@row) = 10, "October", IF(MONTH([Request Received Date]@row) = 11, "November", IF(MONTH([Request Received Date]@row) = 12, "December")))))))))))) + " " + YEAR([Request Received Date]@row), "")
Answers
-
Do you NEED to have the month in text, or could you use something along the lines of "07/20" for July of 2020?
-
Hi Paul,
It is resolved I used following
=IFERROR(IF(MONTH([Request Received Date]@row) = 1, "January", IF(MONTH([Request Received Date]@row) = 2, "February", IF(MONTH([Request Received Date]@row) = 3, "March", IF(MONTH([Request Received Date]@row) = 4, "April", IF(MONTH([Request Received Date]@row) = 5, "May", IF(MONTH([Request Received Date]@row) = 6, "June", IF(MONTH([Request Received Date]@row) = 7, "July", IF(MONTH([Request Received Date]@row) = 8, "August", IF(MONTH([Request Received Date]@row) = 9, "September", IF(MONTH([Request Received Date]@row) = 10, "October", IF(MONTH([Request Received Date]@row) = 11, "November", IF(MONTH([Request Received Date]@row) = 12, "December")))))))))))) + " " + YEAR([Request Received Date]@row), "")
-
Thanks for this code it helps me a lot. Is there a way to set the code for the entire column instead of dragging the corner and pulling it down?
-
Hi @Larry W
Yes! If you right-click on the cell that contains the formula, you can choose an option to "Convert to Column Formula"
See: Set Formulas for All Rows with Column Formulas
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!