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)
Generally when only time function etc would work if you type in, the culprit is the format of the cell which would be in text. For formula to work, the format of the cell cannot be text
