Search code examples
exceldatecountcell

number of days in date range - excel

I am new to excel and been struggling to find a way to get (count) the number of dates (i.e. 14) from a cells range (i.e. B1:B12) considering that every cell has different date that not in sequence with the one in next cell.

Thanks.

EDIT:

Sorry I forgot to mention that I have time included with the date in each cell like the following:

Row: 1/2/2013 12:00:00 pm,1/2/2013 10:00:00 pm,5/9/2009 5:00:00 pm,1/2/2013,4/10/2013 1:00:00 amI need a way to get number 3 for that row.

Solution

  • Try this

    =SUM(IF(FREQUENCY(B2:B12,B2:B12)>0,1))

    Reference:

    http://office.microsoft.com/en-us/excel-help/count-occurrences-of-values-or-unique-values-in-a-data-range-HP003056118.aspx

    Edited:

    Yes, according to comment from @barry, you can use int to drop the time, but you need to enter it as an array formula

    {=SUM(IF(FREQUENCY(INT(B2:B12),IF(B2:B12<>"",INT(B2:B12)))>0,1))}