Counting the number of days per month where where a job start time was after 10:00

I have and Excel spreadsheet that reports in column A the Date and column B the scheduled start time of each BOOKED classroom session throughout the day. The sessions have fixed start times i.e. 08:00, 10:15, 12:30, etc (each seesion is two hours with 15min gap). If asession was not BOOKED it does not appear in the spreadsheet

I have been given the task of finding out how many days each month there were no sessions BOOKED before 10:00 and after 20:15.
I could do this manually but what a pain... I am starting in Jan 2011.

How best could I achieve the task using Excel to do the work?

Thanks in advance for any help for any help

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

Try these formula’s

=SUMPRODUCT(1/COUNTIF(A4:A105,A4:A105))-COUNTIF(B4:B105,TIME(20,30,0))

And

=SUMPRODUCT(1/COUNTIF(A4:A105,A4:A105))-COUNTIF(B4:B105,TIME(7,0,0))

Sixthsense,
That worked a treat thank you very much,
Could you please explain what is happening in (1/COUNTIF(A4:A105,A4:105), I can see the remainder of the fomulae but not that element.

Kind Regards
M

	A
2	1
3	1
4	2
5	2
6	5
7	5
8	5

=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

Appearance of 1 = 2 times
Appearance of 2 = 2 times
Appearance of 5 = 3 times

So the countif will result COUNTIF(A2:A8,A2:A8)

1 = 2;2
2 = 2;2
5 = 3;3;3

Will be the result of countif 2;2;2;2;3;3;3

So in the above the counif is getting the count of each values multiple times.

When we divide it by 1 like this 1/COUNTIF(A2:A8,A2:A8) then EACH VALUES WILL BE DIVIDED BY ITS COUNT

1 = 0.5 because it appears TWO to times (1/2)
2 = 0.5 because it appears TWO to times (1/2)
5 = 0.33 because it appears THREE to times (1/3)

So the result will be like this.. 0.5;0.5;0.5.0.5;0.33;0.33;0.33

The sum of the above will be 2.