In Numbers count specific month/day occurences between two dates

I don't think there is a simple, compact one-formula solution for something like this in Numbers. Here is a slightly "brute force" (but still quite simple) approach involving an intermediate table to hold the array of dates in the range.

The 'Date Range' and 'Date' tables are self-explanatory, with no formulas.

The 'Date Array' table has several thousand rows, at least as many as you need to include all the dates in the date range.

The formula in A2 of 'Date Array' is:

= IFERROR(IF(AND(Date::$A$1≥Date Range::$A$2,Date::$A$1≤Date Range::$B$2),Date::$A$1,1/0),"")

The formula in A3 of 'Date Array' and filled down the rest of column A is:

=IFERROR(IF(AND(A2+1≥Date Range::$A$2,A2+1≤Date Range::$B$2),A2+1,1/0),"")

The formula in B2 of 'Date Array' filled down column B is:

=IFERROR(MONTH(A2),"")

The formula in C2 of 'Date Array' filled down column C is:

=IFERROR(DAY(A2),"")

The formula in A1 of the 'Count' table is:

=COUNTIFS(Date Array::$B,MONTH(Date::$A$1),Date Array::$C,DAY(Date::$A$1))

Substitute ; for , in the formulas if your regions uses , as a decimal separator.

SG

If that was the correct interpretation, best I can think of is a brute force method:

C2 =YEAR(B2)

C3 =C2+1

Fill down from C2 to complete the column

D2 =DATE(C2,MONTH(B$4),DAY(B$4))

E2 =DAY(D2)

Fill down to complete the columns

B5 =COUNTIFS(D,">="&B2,D,"<="&B3,E,DAY(B4))

You can move columns C-E to another table on a different sheet to get it out of the way. You need it to have enough rows to handle the maximum span of start-to-end dates you will ever have.

Hmm. This might take some thought. To be clear on the requirements, let me ask via two examples:

The start date is Jan 1, 2020

The end date is March 1, 2024

I pick Feb 28, 2020 so we are looking for the day Feb 28

The found dates are

Feb 28, 2020

Feb 28, 2021

Feb 28, 2022

Feb 28 2023

Feb 28 2024

Count is 5

Another example:

Same start and end dates as before

I pick Feb 29, 2020

The found dates are

Feb 29, 2020

(No Feb 29 in 2021, 2022, 2023)

Feb 29, 2024

Count is 2

Is this correct?

I haven't had time to test this but a more compact approach something like this might also work:

=IF(OR(Date::$A$1<Date Range::$A$2,Date::$A$1>Date Range::$B$2),0,DATEDIF(EOMONTH(Date::$A$1,−1),Date Range::B2,"Y"))

SG