count unique dates by weekday

[guycothal]

Hey all,

I have a conundrum. I have a list of dates where I have received calls at my job. I can get 10-30 calls a day. I am looking for a formula to tell me how many Mondays, tuesdays, etc I have taken calls. I can't assume that i am working every monday between 2 dates cause I do have time off. Thanks ahead of time for any help.

 

[igold]

Hi guycothal,

Welcome to the MrExcel Forum.

You have to understand that no one can see your worksheet, we have no where your data is located, how it is formatted and exactly what data you have. There is no way anyone can help you unless you give us more information about your worksheets.

You can explain it better, put a sample online at a file sharing site such as Dropbox, whichever, but we need a lot more information...

 

[Tetra201]

Assuming you have your list of call dates (and times?) in range A2:A1000 and your list of dates off in range H2:H100, try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUM(--(FREQUENCY(IF((WEEKDAY(A2:A1000)=2)*(NOT(ISNUMBER(MATCH(INT(A2:A1000),H2:H100,0)))),INT(A2:A1000),FALSE),IF((WEEKDAY(A2:A1000)=2)*(NOT(ISNUMBER(MATCH(INT(A2:A1000),H2:H100,0)))),INT(A2:A1000),FALSE))>0))

where 2 means Monday, 3 means Tuesday, etc.