Highlighting Cell Depend On two criteria date and time

Hi All,

Hope anyone helpout me with it i have a workbook that have multiple sheets and a summary sheet ,
what i want in summary sheet
On Appointment time wise sheet we need date wise and time wise count of appointment generated on particular day. Total require count of appointment in specific time slot is 5. So if 4 appointment are there it should show green and as it cross 5 it should become red.

Any help would be appreciated.

thanks in advance.

See attached.

General formula

=COUNTIFS(Pune_June!$B:$B,$A3,Pune_June!$P:$P,">" &TIME(10,0,0),Pune_June!$P:$P,"<=" &TIME(12,0,0))

=COUNTIFS(Delhi_June!$B:$B,$A3,Delhi_June!$C:$C,">" &TIME(10,0,0),Delhi_June!$C:$C,"<=" &TIME(12,0,0))

Adjust Times for other columns

I amended all your erroneous times: please ensure any future times are entered as hh:mm:ss (or hh:mm)

Perhaps use a Data Validation list (07:00 to 20:00) to ensure all entries are valid times.

Dear John,

Thank you for such a nice solution , yes i agree its my responsibility and your suggestion is perfect for me i will the same which you have suggested , thanks again sir.