# if(and( question

#### jerrywoods

##### New Member
c1 through bc1 column headings are 08:00 am thru 9:00 PM in increments of 15 minutes.

a1 & b1 headings are in, out respectively.

What I want is to check if time entered in a2 is >= to the time c1 and that time in a2 is <=c1.

If both statements are true, I want to enter 1 in c2 otherwise enter a zero.

this is the formula I entered and copied throughout the spreadsheet. =IF(AND(\$A2>=C1,\$B2>=C1),0,1).

there are some problems. Would appreciate some assistance.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### lenze

##### Legend
Hi and welcome to the Board!!!
Maybe this will work, but I'm not sure what you really want.
Code:
=IF((\$A2>=C\$1)*(\$B2>=C\$1),1,0)
lenze

#### scottylad2

##### Well-known Member
Could you display some of your data and what your trying to acheive? Copying your data and that formula, i can make it produce all 1's. are you trying to show a 1 for all the hours someone is in work? and a 0 for when they aren't?

Show some data and i'm sure the guys on here can help you out

#### jerrywoods

##### New Member
would like the cells to show 1 when they worked and 0 when they didn't work.
a b c .............. bc1
in out 08:00am etc... 9:00pm
1 08:00 am 1:00 pm 1 0

There seems to be problems on the ending times. For example an ending time of 8:00 pm only produces a 1 up to 7:45PM not all the way to 8:00 pm.

Last edited:

#### scottylad2

##### Well-known Member
Running the formula you are trying to work with is always going to give you a wrong answer as it always evaluates to either FALSE after the first time and TRUE later on or vice versa. if what your trying to do is get 1's for all the hours between the in and out times you'l either need to end up with a TRUE TRUE or FALSE FALSE evaluation. Because the starting time isn't fixed, i'm not sure what your looking for is easy to come by with your present layout. Maybe a table and 2 vlookups....?

#### jerrywoods

##### New Member
have loaded excel 2007 file to box.net. the url is:

http://www.box.net/shared/9hgkvazft6

Row three works out just like i want it to. perhaps my mistakes are in the copying and pasting using the \$ signs.

This stuff is still pretty new to me...be patient please.

Jerry

Replies
7
Views
249
Replies
1
Views
241
Replies
8
Views
828
Replies
0
Views
313
Replies
8
Views
1K

Threads
1,191,170
Messages
5,985,066
Members
439,938
Latest member
MAlhash

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back