if between statement

J

Jimbo

I have a staffing spreadsheet and I need to have a multiple if
statement and I cannot figure it out.

I have A1 7:00 am, B2 4:00 PM B3 & B4 Break start and stop times, B5
& B6 Lunch break times, B7 & B8 Break times.

I have the Following columns set in 15 minute increments.

I want to receive a value of a "1", if It falls between A1 & B1, but
blank if it falls between breaks and lunches.


Your help would be appreciated.

Thank you
 
J

joeu2004

I have A1 7:00 am, B2 4:00 PM  B3 & B4 Break start and
stop times, B5 & B6 Lunch break times, B7 & B8 Break times.
I have the Following columns set in 15 minute increments.
I want to receive a value of a "1", if It falls between
A1 & B1, but blank if it falls between breaks and lunches.

If the time to compare is in C1:

=IF(OR(AND(A1<=C1,C1<=B3),AND(B4<=C1,C1<=B5),AND(B6<=C1,C1<=B7),
AND(B8<=C1,C1<=B2)),1,"")
 
J

joeu2004

Errata....

If the time to compare is in C1:
=IF(OR(AND(A1<=C1,C1<=B3),AND(B4<=C1,C1<=B5),AND(B6<=C1,C1<=B7),
AND(B8<=C1,C1<=B2)),1,"")

So that the formula can be copied down or across:

=IF(OR(AND($A$1<=C1,C1<=$B$3),AND($B$4<=C1,C1<=$B$5),
AND($B$6<=C1,C1<=$B$7),AND($B$8<=C1,C1<=$B$2)),1,"")
 
D

DCG-jaeson

Hi Sir,

Put this in Cell C2, then Copy accross the end-time of Work.

=IF(AND(C1>=$B$3,C1<=$B$4),"",IF(AND(C1>=$B$5,C1<=$B$6),"",IF(AND(C1>=
$B$7,C1<=$B$8),"",1)))

P.S. You are a lucky Staff due to you have 3 Times Break!!! LOL, hope
we also. Hehe!

Hope it will help!
~jaeson
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top