Counting days

Jayfaku

New Member
Joined
Aug 21, 2018
Messages
16
In column B2:B157 I have dates
In column C2:C157 I have =CHOOSE(WEEKDAY(B3),"Sun","Mon","Tue","Wed","Thu","Fri","Sat","Sun"), to give me the day, B3 is a specific date

What I want to do in another worksheet is count number of mon, tues, wed,... in a given month.

So the result should tell me 5 reports made on Mon in June, etc.....

Can you please help?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
.
Paste in F2 and copy down :
Code:
=COUNTIF(C2:C157,E2)




[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][th]
F
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1
[/td][td][/td][td]
[/td][td]
[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2
[/td][td][/td][td]
1/1/2018​
[/td][td]
Tue​
[/td][td][/td][td]Mon[/td][td]
22​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3
[/td][td][/td][td]
1/2/2018​
[/td][td]
Wed​
[/td][td][/td][td]Tue[/td][td]
22​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4
[/td][td][/td][td]
1/3/2018​
[/td][td]
Thu​
[/td][td][/td][td]Wed[/td][td]
21​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5
[/td][td][/td][td]
1/4/2018​
[/td][td]
Fri​
[/td][td][/td][td]Thu[/td][td]
21​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
6
[/td][td][/td][td]
1/5/2018​
[/td][td]
Sat​
[/td][td][/td][td]Fri[/td][td]
21​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
7
[/td][td][/td][td]
1/6/2018​
[/td][td]
Sun​
[/td][td][/td][td]Sat[/td][td]
22​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
8
[/td][td][/td][td]
1/7/2018​
[/td][td]
Mon​
[/td][td][/td][td]Sun[/td][td]
21​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
9
[/td][td][/td][td]
1/8/2018​
[/td][td]
Tue​
[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
10
[/td][td][/td][td]
1/9/2018​
[/td][td]
Wed​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
In column B2:B157 I have dates
In column C2:C157 I have =CHOOSE(WEEKDAY(B3),"Sun","Mon","Tue","Wed","Thu","Fri","Sat","Sun"), to give me the day, B3 is a specific date
If you really need this (text) column, a much simpler formula is

=TEXT(B3,"ddd")



.. count number of mon, tues, wed,... in a given month.
Can you confirm whether you want ..
a) The actual number of Mondays in a calendar of a particular month, or
b) The number of dates in your list B2:B157 that are actually, say, a Monday in June? If this is it, does your list of dates cover dates in more than one year so that you might have 4 June 2018 and 26 June 2016 and if so, might we count both of thos if looking at June or would we be just looking at, say, June 2018?
 
Upvote 0
The year runs from July 2017 to June 2018, so month is not repeated.
The result should give me NUMBER of reports in June that was on Mon, Tues...

Just to help me understand which day it is being reported I have entered the day formula in column C

Thanks
 
Upvote 0
Just to help me understand which day it is being reported I have entered the day formula in column C
Fair enough, though if you want that, the formula I gave is a very simple one to do that.

For your counting question I have used those column C values in my column F formula below, but note the alternative (shorter) formula in column G that doesn't require column C to get the same result. :)

Excel Workbook
BCDEFG
1June
21/05/2018TueMon22
33/05/2018ThuTue11
45/05/2018SatWed22
57/05/2018MonThu11
69/05/2018WedFri11
711/05/2018FriSat22
813/05/2018SunSun11
915/05/2018Tue
1017/05/2018Thu
1119/05/2018Sat
1221/05/2018Mon
1323/05/2018Wed
1425/05/2018Fri
1527/05/2018Sun
1629/05/2018Tue
1731/05/2018Thu
182/06/2018Sat
194/06/2018Mon
206/06/2018Wed
218/06/2018Fri
2210/06/2018Sun
2312/06/2018Tue
2414/06/2018Thu
2516/06/2018Sat
2618/06/2018Mon
2720/06/2018Wed
Dates
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top