How do I count the number of unique dates in Excel?

Applies To

  • Excel
  • Custom reporting

Answer

  • You must first convert all dates listed to an integer with the formula =int(A1) (replace A1 with correct cell, then copy down column)
  • Then use this formula to count the number of unique values: =SUMPRODUCT(1/COUNTIF(A10:A30,A10:A30))
  • Replace the "A1:A30" with the correct range in your report

Additional Notes

This is a useful formula if you have a report that covers a large date range and you need to find how many days someone worked, or calculate average hours worked per day.