Check Box if One of Two Date Fields Is In Current Month
I have Ship Date and Pickup Date fields and want the Current Month field (checkbox) to check if either Ship Date or Pickup Date is in the current month. The Ship Date is empty when the Pickup Date field has a date and vice versa.
This formula returns #Invalid Data Type when the Ship Date is empty: IF(AND(MONTH(TODAY()) - MONTH([Ship Date]@row) = 0, YEAR(TODAY()) = YEAR([Ship Date]@row)), 1, IF(AND(MONTH(TODAY()) - MONTH([On-Site Pickup Date]@row) = 0, YEAR(TODAY()) = YEAR([On-Site Pickup Date]@row)), 1, ""))
Thanks for your help!
Lori Flanigan
Best Answer
-
If the Ship Date is empty there is no MONTH or YEAR for Ship Date so the formula will fail as it has nothing to subtract. If you wrap the MONTH and YEAR in IFERROR functions to return 0s if the date is blank, the error will be removed:
=IF(AND(MONTH(TODAY()) - IFERROR(MONTH([Ship Date]@row), 0) = 0, YEAR(TODAY()) = IFERROR(YEAR([Ship Date]@row), 0)), 1, IF(AND(MONTH(TODAY()) - MONTH([On-Site Pickup Date]@row) = 0, YEAR(TODAY()) = YEAR([On-Site Pickup Date]@row)), 1, ""))
If the date is blank the formula will take today's month/year and subtract 0 instead. Which will be no equal to 0.
However, you will then have an error if there is a date in Ship Date and it is not the current month. So you also need to add IFERROR to the Pickup Date part as well.
=IF(AND(MONTH(TODAY()) - IFERROR(MONTH([Ship Date]@row), 0) = 0, YEAR(TODAY()) = IFERROR(YEAR([Ship Date]@row), 0)), 1, IF(AND(MONTH(TODAY()) - IFERROR(MONTH([On-Site Pickup Date]@row), 0) = 0, YEAR(TODAY()) = IFERROR(YEAR([On-Site Pickup Date]@row), 0)), 1, ""))
Add these 4 IFERRORs and it should be good.
Answers
-
If the Ship Date is empty there is no MONTH or YEAR for Ship Date so the formula will fail as it has nothing to subtract. If you wrap the MONTH and YEAR in IFERROR functions to return 0s if the date is blank, the error will be removed:
=IF(AND(MONTH(TODAY()) - IFERROR(MONTH([Ship Date]@row), 0) = 0, YEAR(TODAY()) = IFERROR(YEAR([Ship Date]@row), 0)), 1, IF(AND(MONTH(TODAY()) - MONTH([On-Site Pickup Date]@row) = 0, YEAR(TODAY()) = YEAR([On-Site Pickup Date]@row)), 1, ""))
If the date is blank the formula will take today's month/year and subtract 0 instead. Which will be no equal to 0.
However, you will then have an error if there is a date in Ship Date and it is not the current month. So you also need to add IFERROR to the Pickup Date part as well.
=IF(AND(MONTH(TODAY()) - IFERROR(MONTH([Ship Date]@row), 0) = 0, YEAR(TODAY()) = IFERROR(YEAR([Ship Date]@row), 0)), 1, IF(AND(MONTH(TODAY()) - IFERROR(MONTH([On-Site Pickup Date]@row), 0) = 0, YEAR(TODAY()) = IFERROR(YEAR([On-Site Pickup Date]@row), 0)), 1, ""))
Add these 4 IFERRORs and it should be good.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!