# "COUNTIF" function for day of the week formatting

Closed
hangomango Posts 2 Registration date Thursday January 23, 2014 Status Member Last seen January 23, 2014 - Jan 23, 2014 at 05:47 PM
Jason - Aug 8, 2014 at 11:41 AM
Hello there,

Please help! I have a column of dates, where the formatting I've set for "Day of the week, month, day, year".

How would I run a COUNTIF function for "Monday"s, "Tuesday"s...etc? I tried running it, but because the formatting in the formula bar is ex: "01/02/13", it doesn't pick up the text in the formatted column. My formula, =COUNTIF(range, "Monday") returns "0" even though there are about 125 instances.

Thanks so much for everyone's help in advance! Have a great one!
Related:

## 2 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jan 31, 2014 at 06:57 AM
you have to convert dates into excel dates. in your data there are two mistakes.
1/12/13 is entered as "1/12/13"
to remove those quotation marks use
substitue(a1,"""","")
=SUBSTITUTE(A1,"""","")

then there MAY be another mistake.find out in your regional configuration how is date entered. In some regions it is m/d/yy, in some other it is d/m/yy.
check control panel-regions and language and see how short date is entered.

then use weekday(A1). see help weekday
Ok, my 2 cents here. What I did for a similar concept (I was trying to create a histogram of how often an event occurred on a given day of the week).

Col1 = Date Col2 = weekday(Col1) function

8/8/2014 10:33 6
...
4/3/2026 20:02 7
-----------------------------------
I created a table had day of the week in the first column and countif in the second

Col1 Col2
Sun =COUNTIF(\$N\$13:\$N\$100,1)
Mon =COUNTIF(\$N\$13:\$N\$100,2)
Tues =COUNTIF(\$N\$13:\$N\$100,3)
Wed =COUNTIF(\$N\$13:\$N\$100,4)
Thu =COUNTIF(\$N\$13:\$N\$100,5)
Fri =COUNTIF(\$N\$13:\$N\$100,6)
Sat =COUNTIF(\$N\$13:\$N\$100,7)

--------------------------------------------

This created a table I could graph or visibly look at