Counting unique Dates without the time affecting the outcome

[zanzi]

I am trying to count the number of days worked in a list the has different activities listed at different times in the day. Because of the different times in a single day I am having trouble getting a count of the days only.

I used a formula from another posting ......

=SUM(IF(LEN(B29:B100),1/COUNTIF(B29:B100,B29:B100)))

but it treats each date and time as a unique entity. I need to just count the days but am stuck with data that also has times attached. Is there any way around this?

example

4/12/04 12:00 AM
4/12/04 12:00 AM
4/13/04 12:00 AM
4/13/04 5:57 PM
4/13/04 8:37 PM
4/14/04 12:00 AM
4/14/04 6:26 PM
4/14/04 6:28 PM
4/14/04 6:28 PM
4/15/04 12:00 AM
4/15/04 7:29 PM
4/15/04 7:29 PM
4/16/04 12:00 AM
4/16/04 8:37 PM
4/17/04 12:00 AM
4/17/04 3:52 PM

 

[Aladin Akyurek]

Re: Counting unique Dates without the time affecting the out

Only date values. (mm/dd/yyyy)

The data can be sorted in ascending value, but I am trying to figure out a solution flexible enough to accomodate it if one or two date records are out of order.

I kinda have a rough solution, combining the code provided above, and also using the MATCH function to determine the first and last row ids of the range, and then concatenating the fields to create a range, but it's not very clean, and I'm sure there are situations where it may not be accurate. (ie. when the data is not in ascending order)

Book15
	A	B	C	D	E
1			20
2	1/4/2004		Ifsortedinascendingorder
3	1/15/2004		2/15/2004	6	2/16/2004
4	1/28/2004		3/16/2004	12	3/15/2004
5	2/3/2004		4
6	2/16/2004
7	2/16/2004		Ifunsorted&morefuncavailable
8	2/28/2004		4
9	2/28/2004
10	3/5/2004
11	3/5/2004
12	3/15/2004
13	3/17/2004
14	3/19/2004
15	3/19/2004
16	3/28/2004
17	4/4/2004
18	4/7/2004
19	4/11/2004
20	5/1/2004
Sheet1

Formulas...

C1:

=MATCH(9.99999999999999E+307,A:A)

If column A is set in ascending order (a prerequisite)...

D2:

=MIN(IF((A2:INDEX(A:A,C1)>=C3),ROW(A2:INDEX(A:A,C1))))

which must be confirmed with control+shift+enter instead of just enter.

D3:

=MATCH(C4,A:A)

E3, copied to E4...

=INDEX(A:A,D3)

in order to show from which date to which date the computation is really done.

C5:

=SUMPRODUCT(1/COUNTIF(OFFSET($A$1,D3-1,0,D4-D3+1,1),OFFSET($A$1,D3-1,0,D4-D3+1,1)))

which does effect a conditional unique date count.

If column A is unsorted and the morefunc.xll add-in is available...

C8:

=COUNT(UNIQUEVALUES(IF((A2:INDEX(A:A,C1)>=C3)*(A2:INDEX(A:A,C1)<=C4),A2:INDEX(A:A,C1))))

which must be confirmed with control+shift+enter instead of just with enter.

 

[jlu21]

Re: Counting unique Dates without the time affecting the out

Thanks!!