# 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, ""))

Lori Flanigan

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!