# Count duplicate dates within a formula (no ranges)



## Retroshift (Dec 21, 2022)

Hi
I have a reference cell B2 which contains the year.
I would like to create a formula that contains dates (not referenced in cells) and counts the duplicate dates.

Within the formula I have the fixed dates _DATE(YEAR(B2),1,1),DATE(YEAR(B2),3,2),DATE(YEAR(B2),5,1),DATE(YEAR(B2),5,9)_ and I would like to check them off against some floating fictitious UDF dates _EasterDate(YEAR(B2))-10,EasterDate(YEAR(B2))-5_. Depending on the year in cell B2, some dates might overlap. Is there a formula/array to count/sum up these duplicate dates within the formula?


----------



## jdellasala (Dec 22, 2022)

If the value in B2 is the number 2022 or even YEAR(DATEVALUE(1/1/2022)), the value of *DATE(YEAR(B2),5,1)* is *05/01/1905*. Is that really what you want/need? Perhaps you could paste a Mini Table using *XL2BB*?


----------



## Retroshift (Dec 23, 2022)

What I would like is to have a workaround which makes (date) formulas work as array constants within a formula, instead of ranges.
Cell B2 contains a dynamic year.
I would like to count the possible duplicates between the first four dates as compared to the last two (easter)dates.
Below is the formula which I would like to make work through a workaround: is there a way to return the dates as constants so they could be compared to each other (in an array)?


```
=SUMPRODUCT(1/COUNTIF((DATE(YEAR(B2),1,1),DATE(YEAR(B2),3,2),DATE(YEAR(B2),5,1),DATE(YEAR(B2),5,9)),(EasterDate(YEAR(B2))-10,EasterDate(YEAR(B2))-5)))
```

I have Excel 2019.


----------

