if(and( question

jerrywoods

New Member
Joined
Feb 12, 2008
Messages
12
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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....?
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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

Which adblocker are you using?

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
Back
Top