Count Days?

  • Thread starter Thread starter wx4usa
  • Start date Start date
W

wx4usa

Is there a way to count the specific instances/occurances of dates?

I may run as many as 3 delivery trucks on a specific day. On some days
no trucks run. I want to count how many days in a month I deliver.
Without counting the multiples.

I have a list of dates in column A .
Column a represents data about each truck run.
The other columns show other delivery related data.

01/02/06
01/02/06
01/03/06
01/05/06
02/05/06
02/15/06

etc

Is there a way to count the number of days represented/delivered in
January (or any specified month) 2006 WITHOUT counting January 2nd
2X's?

Just the number of days out of 31 that have shown activity?
 
Say your dates are in A1 to A100.

Enter the month you're looking to count into D1, using the format of "mmm"
(Jan, Feb, Mar, ...etc.).

Then try this *array* formula:

=COUNT(1/FREQUENCY(IF((TEXT(A1:A100,"mmm")=D1)*(A1:A100<>""),MATCH(A1:A100,A
1:A100,0)),ROW(1:100)))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

Note: This will count all unique dates for a particular month, no matter
what the year.!
 
RD,

Thanks for the quick reply, however I could not get it to work.

My column A represents each delivery route. I may have 90 delivery
route entries in January, but I may only actually be running trucks on
23 days in January.

There will be many days that are duplicated because I am running 3-4
trucks on that day, but I want excel to count that day as 1.

If I run 1 truck, it should count as 1. If I run multiple trucks, it
should count as 1, if I run no trucks, it should count as 0.

How do I count just the specific days in January 2006 I was delivering?
 
What does "not get it to work" mean?

No answer - Wrong answer - Error message?

The array formula that I suggested will count unique dates entered in Column
A, from A1 to A100, where the date matches the month that is entered into
cell D1 in the 3 letter abbreviated format of the example in my post (Jan -
Feb).

ALSO, the dates must be true XL recognized dates, which also means that
there can be *nothing else* in the cell, except the date ... And the formula
must be entered using CSE (<Ctrl> <Shift> <Enter>).

Be more explicit as to what is "not working"!
 
What does RD's formula return for you?

Works for me if entered in any cell but D1.

Don't forget the CTRL + SHIFT + ENTER

Enter Jan in D1


Gord Dibben MS Excel MVP
 
Here are the only 3 dates I have entered yet in column A. The formula
returns 1.

I can send you the small worksheet to look at. I do have the dates in
a5:a100 and I changed those in the formula you sent me. THe dates
below are in a5:a7 the remainder is blank at this time. I will
eventually have up to 3000 date lines.

I did array enter it too.

1/31/2006
1/30/2006
12/20/2006
 
I put your dates in A1:A3 and JAN in D1 (not a date, just the letters JAN in
D1).

RD's formula returned 2.

It returned 1 if I didn't hit ctrl-shift-enter, though.
 
Back
Top