Check Box if One of Two Date Fields Is In Current Month

Lori Flanigan

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

KPH

Hi @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.

KPH

Hi @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.