If statement formulas

[Unknown Soldier]

I have two sheets in a workbook. One called Availability, and the other
called schedule. The availability tells what time my employees are
available to work and it look like this:
A1 B1 C1
Name Start End
Tom 6 AM 2 PM
Jason 9 AM 5 PM
John 1 PM 9 Pm
Susan 8 AM 4 PM


In the schedule sheet look similar to availability sheet accept it has one
more column that will if I schedule an employee that will have a time
conflict with availablity sheet. Look like this.

A1 B1 C1 D1 E1
F1
Name Start End
Jason 8AM 5 PM Not available before 9 am
Susan 8 AM 6 PM Not Available after 4 pm
John 6 Am 12 PM Only available from 1pm to 9 pm
Tom 6 AM 2 PM

Notice I added three more column. One tell a statement such as "Not
Available Before", "Not Available After", and "Only Available From". The
other two column tell if Column D has a statement such as "Not Available
before", then column E specify the time that particular employee not
available before what time. Similary, with "Not Available after" column E
specify the time that particular employee not available after. Similary, if
column D statement says the time is out of range or "Only Availbable From",
specify the start time in E column and end time in F column. If time is
within range then don't do anything or display blank. Notice the names of
my employees in shedule sheet are not in order with the name of my employees
in the availability sheet. I probably need vlookup formulas as well?

What formulas do I put in cells of Column D, E, F the get the deserve effect
from above? Thanks

 

[Rick Rothstein \(MVP - VB\)]

Give this a try...

Put this in D2
==================
=IF(A2="","",IF(AND(B2>=INDEX(Availability!A$2:C$100,MATCH(A2,Availability!A$2:A$5,0),2),C2<=INDEX(Availability!A$2:C$100,MATCH(A2,Availability!A$2:A$5,0),3)),"",IF(OR(B2>INDEX(Availability!A$2:C$100,MATCH(A2,Availability!A$2:A$5,0),3),C2<INDEX(Availability!A$2:C$100,MATCH(A2,Availability!A$2:A$5,0),2)),"Only
available
from",IF(B2<INDEX(Availability!A$2:C$100,MATCH(A2,Availability!A$2:A$5,0),2),"Not
available before","Not available after"))))

Put this in E2
==================
=IF(A2="","",IF(D2="Not available
before",INDEX(Availability!A$2:C$100,MATCH(A2,Availability!A$2:A$5,0),2),IF(D2="Not
available
after",INDEX(Availability!A$2:C$100,MATCH(A2,Availability!A$2:A$5,0),3),IF(D2<>"",INDEX(Availability!A$2:C$100,MATCH(A2,Availability!A$2:A$5,0),2),""))))

Put this in F2
==================
=IF(D2="Only available from"," to
"&TEXT(INDEX(Availability!A$2:C$100,MATCH(A2,Availability!A$2:A$5,0),3),"h:mm
AM/PM"),"")

An then copy them down. As written, the formulas will work down to Row 100.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Just to clarify my "the formulas will work down to Row 100"... that is these
formulas lookup names and times down to Row 100 on the Availability
worksheet... the formulas themselves can be copied down on the Schedule
sheet as far as needed.

Rick

 

[Unknown Soldier]

It does not seem to work right.

Here is the sample result. For the imformation in the availability sheet:

Tom 6:00 AM 2:00 PM
Jason 9:00 AM 5:00 PM
John 1:00 PM 9:00 PM
Dave 8:00 AM 4:00 PM


In the schedule sheet

Jason 9:00 AM 5:00 PM
Tom 10:00 AM 10:00 PM Not available after 6:00 AM
Dave 8:00 AM 3:00 PM
John 7:00 AM 4:00 PM Not available before 1:00 PM


For Tom it should display not available after 2:00 PM. It there is bug
somewhere. The formulas is so long that I got dizzy just by looking at
it...hehehehe

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure what to tell you... on my system, with the data you posted, I
get "Not available after 2:00 PM" just as you say I should. If it helps you
any, I posted a copy of my spreadsheet with these formulas, and your data,
showing this result; here is the link....

http://www.rickrothstein.com/temp/Availabiliy.xls

If you have trouble opening it, then download it instead.

Rick

 

[Unknown Soldier]

Thanks so much Rick

However, here is the twist. In the availability sheet, I only have
availability for 4 of my employees in a single day only which is Monday. I
want to go across and fill their availablity from Tues day to sunday in the
availability sheet.

Simililary on schedule sheet, I want to go across and fill their schedule
from Tues-Sun.

To result the time conflict, I created a three sheet Call "Conflict" and I
put the the three formulas you gave me under Monday and stress them across
to Sunday in relations with availability and schedule sheet. How do I do
this since the formulas you gave me only work for monday?

Thanks

 

[Rick Rothstein \(MVP - VB\)]

Your initial post mentioned nothing about "days"... just 3 columns. For
future reference when asking questions on newsgroups... don't simply your
needs when asking your question... as you can see, you will more than likely
not get an answer that you can extrapolate to meet your full requirements.
Now, so we don't have to guess, show us how your columns are laid out. Also,
is there something more that you are hinting at when you say "I only have
availability for 4 of my employees in a single day"? Are there more
employees that need to be accounted for, but in some different way?

Rick

 

[Unknown Soldier]

I apology, I did not know more problems arrive as more needs come to mind
for the sheets I need. However, here it is.

In the availability sheet it look like this:

Monday Tuesday Wednesday Thursday Friday Saturday Sunday
Tom 6:00 AM 2:00 PM 6:00 AM 2:00 PM 6:00 AM 2:00 PM 6:00 AM 2:00 PM
1:00 PM 9:00 PM 1:00 PM 9:00 PM 1:00 PM 9:00 PM
Jason 9:00 AM 5:00 PM 9:00 AM 5:00 PM 9:00 AM 5:00 PM 9:00 AM 5:00 PM
9:00 AM 5:00 PM 1:00 PM 9:00 PM 1:00 PM 9:00 PM
John 1:00 PM 9:00 PM 1:00 PM 9:00 PM 1:00 PM 9:00 PM 1:00 PM 9:00 PM
1:00 PM 9:00 PM 6:00 AM 2:00 PM 6:00 AM 2:00 PM
Dave 8:00 AM 4:00 PM 8:00 AM 4:00 PM 8:00 AM 4:00 PM 8:00 AM 4:00 PM
12:00 PM 8:00 PM 12:00 PM 8:00 PM 12:00 PM 8:00 PM


In the schedule sheet it look like this:

Monday Tuesday Wednesday Thursday Friday Saturday Sunday
Jason 9:00 AM 5:00 PM 9:00 AM 5:00 PM 9:00 AM 5:00 PM 9:00 AM 5:00 PM
1:00 PM 9:00 PM 1:00 PM 9:00 PM 1:00 PM 9:00 PM
Tom 10:00 AM 10:00 PM 10:00 AM 10:00 PM 10:00 AM 10:00 PM 10:00 AM
10:00 PM 10:00 AM 10:00 PM 2:00 PM 8:00 PM 2:00 PM 8:00 PM
Dave 8:00 AM 3:00 PM 8:00 AM 3:00 PM 8:00 AM 3:00 PM 8:00 AM 3:00 PM
8:00 AM 3:00 PM 12:00 PM 6:00 PM 12:00 PM 6:00 PM
John 12:00 PM 6:00 PM 12:00 PM 6:00 PM 12:00 PM 6:00 PM 12:00 PM 6:00
PM 12:00 PM 6:00 PM 1:00 PM 7:00 PM 1:00 PM 7:00 PM


In the time conflict sheet it should look like this. I only got the first
column of this sheet for demostration purposes, which is monday.
Monday Tuesday Wednesday Thursday Friday Saturday Sunday
Jason N\A after 2:00 PM 9:00 AM 5:00 PM 9:00 AM 5:00 PM 9:00 AM
5:00 PM 1:00 PM 9:00 PM 1:00 PM 9:00 PM 1:00 PM 9:00 PM
Tom Only Available from 9:00 AM to 5:00:00 PM 10:00 AM 10:00 PM
10:00 AM 10:00 PM 10:00 AM 10:00 PM 10:00 AM 10:00 PM 2:00 PM 8:00 PM
2:00 PM 8:00 PM
Dave 8:00 AM 3:00 PM 8:00 AM 3:00 PM 8:00 AM 3:00 PM 8:00 AM 3:00
PM 8:00 AM 3:00 PM 12:00 PM 6:00 PM 12:00 PM 6:00 PM
John N\A before 1:00 PM 12:00 PM 6:00 PM 12:00 PM 6:00 PM 12:00
PM 6:00 PM 12:00 PM 6:00 PM 1:00 PM 7:00 PM 1:00 PM 7:00 PM



Any helps is greatly appreciated. I have a hard time understand the
formulas. I got my head spinning just by look at the length of it.

 

[Rick Rothstein \(MVP - VB\)]

Okay, I see another change from your original post (which, I think, will
also affect how I restructure my original formulas). In your original post,
you said you only had two sheets (named Availability and Schedule), but now
I see a third sheet listed (named Conflicts). It looks like what you
originally asked to go in columns D, E and F right next to each time span
now goes into this previously unmentioned worksheet. If that is so, you will
need to clarify it layout a little more for us. It looks like you have a
mixture of warning messages and valid scheduled time spans across the days
of the week. If that is correct, how are you handling the 3rd column (F from
the D, E, F columns reserved for the warning messages) as it relates to the
valid time spans? Does each day on the Conflicts sheet span 3 columns
whereas on the other 2 days they span on 2 columns? The more detail you can
give us on this part of the layout, the better (note that the information
you last posted is not spaced very well and it hard to read for its actual
layout.

Rick

 

[Unknown Soldier]

Yes, there is a new sheet named "Conflict", and the formulas you wrote for
me early is moved to these sheet, but I can't them to work because of the
changes in locations. Yes, Each day of the week for the Conflict sheet has
three column. One display a warning messsage such as N\A before, N\A after,
or Only Available from. The column next to it will display the time before
or after depend on the the warning column, and the last column will play "To
and the time" if the warning column has a warning message such as "only
available from". Yes, the Availability sheet, and the schedule sheet only
has 2 column under each day. One for the time start. One for the time end.
They also span for 7 days instead of 1 day as was in the orginal post.
Please, let me know if you need further clarification. Thanks so much.