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

#### Intermediate

##### New Member
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?

#### Intermediate

##### New Member
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

##### MrExcel MVP
Example:

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

#### markmzz

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

Layout

 Dates Weekday Month Year Result 21/08/2013 Saturday November 2013 3 19/07/2013 Monday October 2012 11 16/08/2013 07/08/2012 12/06/2012 04/12/2012 ** ********* ********** ***** ******

<tbody>
</tbody>

Formula

Code:
``````In F2

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

#### markmzz

##### MrExcel MVP
Sorry, no need modification.

Last edited:

#### Intermediate

##### New Member
Thanks guys - far more elegant! I appreciate it.

1,081,726
Messages
5,360,906
Members
400,602
Latest member
newaqua

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...