Find and count days matching particular weekday, particular month and particular year from within a list of random dates

[Intermediate]

Hi,

I have a random list of dates from multiple years in column A:A. I would like to count how many of those random dates are eg. Saturdays in November 2013 or Mondays in October 2012. Any ideas?

Thanks in advance for your time and help.

 

[Intermediate]

As is often the case - having written it out on a forum I've solved it myself. It's not an elegant solution but it does the job. If anyone has a cleaner way, with creating additional columns, please post. My clunky way is as follows:
List of random dates are eg. A1:A1000
Name Column B "DAY", and in B1, =WEEKDAY(A1,2) [2 counts the first day of the week as a Monday instead of Sunday]
Name Column C "MONTH", and in C1, =MONTH(A1)
Name Column D "YEAR", and in D1, =YEAR(A1)
Select B1:D1 and drag down to eg. D1000 to copy formulae alongside dates

In E1, =COUNTIFS(B:B,6,C:C,11,D:D,2013) [this will count rows where Day=Saturday, Month=November, Year=2013]

 

[Andrew Poulsom]

Example:

=SUMPRODUCT(--(TEXT(A1:A1000,"ddd")="Sat"),--(TEXT(A1:A1000,"mmm yyyy")="Nov 2013"))

 

[markmzz]

Maybe this (a small modification in Andrew's formula):

Layout

[TABLE="width: 196"]
<tbody>[TR]
[TD="class: xl63, width: 63, bgcolor: transparent"]Dates
[/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 56, bgcolor: transparent"]Weekday
[/TD]
[TD="class: xl63, width: 56, bgcolor: transparent"]Month
[/TD]
[TD="class: xl63, width: 32, bgcolor: transparent"]Year
[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"]Result
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]21/08/2013
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Saturday
[/TD]
[TD="class: xl66, bgcolor: transparent"]November
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2013
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]19/07/2013
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Monday
[/TD]
[TD="class: xl66, bgcolor: transparent"]October
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2012
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]11
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]16/08/2013
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]07/08/2012
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]12/06/2012
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]04/12/2012
[/TD]
[TD="class: xl63, bgcolor: transparent"]**
[/TD]
[TD="class: xl63, bgcolor: transparent"]*********
[/TD]
[TD="class: xl63, bgcolor: transparent"]**********
[/TD]
[TD="class: xl63, bgcolor: transparent"]*****
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[/TR]
</tbody>[/TABLE]

Formula

In F2

=SUMPRODUCT(--(TEXT($A$2:$A$1000,"ddddmmmmyyyy")=$C2&$D2&$G2))

Markmzz

 

[markmzz]

Sorry, no need modification.

 

[Intermediate]

Thanks guys - far more elegant! I appreciate it.