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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!