Trouble with functions in Excel

Closed
Glitch - Aug 22, 2010 at 03:15 PM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Aug 24, 2010 at 08:22 AM
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

1 reply

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 24, 2010 at 08:22 AM
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
0