Trouble with functions in Excel

Hello,

I'm currently making a spreadsheet which is designed to take the start and end time of a staff members shift and use conditional formatting to show in graphical form the length of their shift.

I'm using the following formula:

=IF(AND(C1>=$A$4)*(C1<$B$4),"1",)

C1 is a time - say 6:30

A4 will be the start of the shift and B4 will be the end. e.g 6:30 and 12:30

When I copy and paste the data into the sheet then the function won't perform and change the number in the current cell to 1. However, if I were to manually enter the start at end times then the function will perform correctly and enter the 1.

Is there any way I can make it so the above function will work simply from copying and pasting information into the appropriate cells? (I would do it manually but doing this for 100 or so cells every day is too time consuming)

Thanks for any help offered.
Glitch