"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
Jason - Aug 8, 2014 at 11:41 AM
Related:
- Function which returns day of week is
- Time of day clock stopped - Guide
- Hay day download pc - Download - Simulation
- Windows calendar show week number - Guide
- Time set day - Guide
- Agp function - Guide
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
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
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
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
