Previous Topic Next Topic

"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 replies

Answer 1 / 2
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 808
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
0
Answer 2 / 2
Jason
Aug 8, 2014 at 11:41 AM
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
0