If statement formulas

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

Mike
Fri Mar 14 11:24:01 CDT 2008

Paste this into cell D1 on the schedule sheet. And drag down column.
=IF(AND(Availability!B1>B1,Availability!C1>C1),"Only available
from"&TEXT(Availability!B1,"h:mm AM/PM")&" to "&TEXT(Availability!C1,"h:mm
AM/PM"),IF(Availability!B1>B1,"Not available
before"&TEXT(Availability!B1,"h:mm AM/PM"),IF(Availability!C1<C1,"Not
available before ."&TEXT(Availability!C1,"h:mm AM/PM"),"Good")))

in cell A1 on the schedule sheet paste this formula And drag down column
=Availability!A1

On the Availability sheet
A B C
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

On the Scheduled sheet
B C D
6:00 AM 2:00 PM 'this column is for the If statement
8:00 AM 5:00 PM
6:00 AM 12:00 PM
8:00 AM 3:00 PM





"Unknown Soldier" wrote:

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

Unknown
Fri Mar 14 12:25:10 CDT 2008

It has a reference problem after I did that


"Mike" <Mike@discussions.microsoft.com> wrote in message
news:95FDDCC4-1C93-46FF-8F48-1B0F6ACA114E@microsoft.com...
> Paste this into cell D1 on the schedule sheet. And drag down column.
> =IF(AND(Availability!B1>B1,Availability!C1>C1),"Only available
> from"&TEXT(Availability!B1,"h:mm AM/PM")&" to "&TEXT(Availability!C1,"h:mm
> AM/PM"),IF(Availability!B1>B1,"Not available
> before"&TEXT(Availability!B1,"h:mm AM/PM"),IF(Availability!C1<C1,"Not
> available before ."&TEXT(Availability!C1,"h:mm AM/PM"),"Good")))
>
> in cell A1 on the schedule sheet paste this formula And drag down column
> =Availability!A1
>
> On the Availability sheet
> A B C
> 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
>
> On the Scheduled sheet
> B C D
> 6:00 AM 2:00 PM 'this column is for the If statement
> 8:00 AM 5:00 PM
> 6:00 AM 12:00 PM
> 8:00 AM 3:00 PM
>
>
>
>
>
> "Unknown Soldier" wrote:
>
>> 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
>>
>>
>>

Mike
Fri Mar 14 12:55:01 CDT 2008

Make sure the formula has correct spelling of the sheet names

"Unknown Soldier" wrote:

> It has a reference problem after I did that
>
>
> "Mike" <Mike@discussions.microsoft.com> wrote in message
> news:95FDDCC4-1C93-46FF-8F48-1B0F6ACA114E@microsoft.com...
> > Paste this into cell D1 on the schedule sheet. And drag down column.
> > =IF(AND(Availability!B1>B1,Availability!C1>C1),"Only available
> > from"&TEXT(Availability!B1,"h:mm AM/PM")&" to "&TEXT(Availability!C1,"h:mm
> > AM/PM"),IF(Availability!B1>B1,"Not available
> > before"&TEXT(Availability!B1,"h:mm AM/PM"),IF(Availability!C1<C1,"Not
> > available before ."&TEXT(Availability!C1,"h:mm AM/PM"),"Good")))
> >
> > in cell A1 on the schedule sheet paste this formula And drag down column
> > =Availability!A1
> >
> > On the Availability sheet
> > A B C
> > 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
> >
> > On the Scheduled sheet
> > B C D
> > 6:00 AM 2:00 PM 'this column is for the If statement
> > 8:00 AM 5:00 PM
> > 6:00 AM 12:00 PM
> > 8:00 AM 3:00 PM
> >
> >
> >
> >
> >
> > "Unknown Soldier" wrote:
> >
> >> 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
Fri Mar 14 14:06:30 CDT 2008

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


"Unknown Soldier" <nomailplease.com> wrote in message
news:47da981f$0$22818$4c368faf@roadrunner.com...
>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
>

TomHutchins
Fri Mar 14 14:13:01 CDT 2008

I have created a workbook based on your example with a user-defined function
that returns the conflict description in column D (I incorporated the
start/end times as appropriate, so there was no need to populate columns E &
F. If you need those times to appear in columns E & F instead, I can help you
do that.)

Here is a link to the sample workbook:

http://www.freefilehosting.net/download/3ddll

Hope this helps,

Hutch

"Unknown Soldier" wrote:

> 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
Fri Mar 14 14:12:37 CDT 2008

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


"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:%23Ued4ZghIHA.748@TK2MSFTNGP04.phx.gbl...
> 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
>
>
> "Unknown Soldier" <nomailplease.com> wrote in message
> news:47da981f$0$22818$4c368faf@roadrunner.com...
>>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
>>
>

Unknown
Fri Mar 14 21:25:41 CDT 2008

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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:%23Ued4ZghIHA.748@TK2MSFTNGP04.phx.gbl...
> 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
>
>
> "Unknown Soldier" <nomailplease.com> wrote in message
> news:47da981f$0$22818$4c368faf@roadrunner.com...
>>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
Fri Mar 14 21:54:41 CDT 2008

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" <nomailplease.com> wrote in message
news:47db33a0$0$4937$4c368faf@roadrunner.com...
> 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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
> message news:%23Ued4ZghIHA.748@TK2MSFTNGP04.phx.gbl...
>> 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
>>
>>
>> "Unknown Soldier" <nomailplease.com> wrote in message
>> news:47da981f$0$22818$4c368faf@roadrunner.com...
>>>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
>>>
>>
>
>

SeanTimmons
Sat Mar 15 09:27:01 CDT 2008

OK, this one should work well. Please note, for formatitng ease, I have
separated the "to into a an extra column, so we go out to column G instead of
F.

Change "Sheet2" to your referenced sheet name.

in cell D2 enter:

=IF(OR(VLOOKUP(A2,Sheet2!A:B,2,FALSE)>C2,VLOOKUP(A2,Sheet2!A:C,3,FALSE)<B2),"Only
Available from",IF(VLOOKUP(A2,Sheet2!A:B,2,FALSE)>B2,"Not Available
before",IF(VLOOKUP(A2,Sheet2!A:C,3,FALSE)<C2,"Not Available
after"&VLOOKUP(A2,Sheet2!A:C,3,FALSE),"")))

in cell E2, enter:

=IF(OR(D2="Not Available before",D2="Only Available
from"),VLOOKUP(A2,Sheet2!A:B,2,FALSE),IF(D2="Not Available
After",VLOOKUP(A2,Sheet2!A:C,3,FALSE),""))

in cell F2, enter:

=IF(D2="Only Available from"," to","")

in cell G2, enter:

=IF(D2="Only Available from",VLOOKUP(A2,Sheet2!A:C,3,FALSE),"")

"Unknown Soldier" wrote:

> 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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
> message news:%23Ued4ZghIHA.748@TK2MSFTNGP04.phx.gbl...
> > 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
> >
> >
> > "Unknown Soldier" <nomailplease.com> wrote in message
> > news:47da981f$0$22818$4c368faf@roadrunner.com...
> >>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
> >>
> >
>
>
>

Unknown
Mon Mar 17 10:41:41 CDT 2008

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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:eXo9efkhIHA.1944@TK2MSFTNGP02.phx.gbl...
> 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" <nomailplease.com> wrote in message
> news:47db33a0$0$4937$4c368faf@roadrunner.com...
>> 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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote
>> in message news:%23Ued4ZghIHA.748@TK2MSFTNGP04.phx.gbl...
>>> 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
>>>
>>>
>>> "Unknown Soldier" <nomailplease.com> wrote in message
>>> news:47da981f$0$22818$4c368faf@roadrunner.com...
>>>>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
Mon Mar 17 11:43:56 CDT 2008

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" <nomailplease.com> wrote in message
news:47de9129$0$1117$4c368faf@roadrunner.com...
> 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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
> message news:eXo9efkhIHA.1944@TK2MSFTNGP02.phx.gbl...
>> 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" <nomailplease.com> wrote in message
>> news:47db33a0$0$4937$4c368faf@roadrunner.com...
>>> 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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote
>>> in message news:%23Ued4ZghIHA.748@TK2MSFTNGP04.phx.gbl...
>>>> 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
>>>>
>>>>
>>>> "Unknown Soldier" <nomailplease.com> wrote in message
>>>> news:47da981f$0$22818$4c368faf@roadrunner.com...
>>>>>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
>>>>>
>>>>
>>>
>>>
>>
>
>

Unknown
Mon Mar 17 22:32:16 CDT 2008

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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:%23zLeW4EiIHA.4536@TK2MSFTNGP06.phx.gbl...
> 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" <nomailplease.com> wrote in message
> news:47de9129$0$1117$4c368faf@roadrunner.com...
>> 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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote
>> in message news:eXo9efkhIHA.1944@TK2MSFTNGP02.phx.gbl...
>>> 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" <nomailplease.com> wrote in message
>>> news:47db33a0$0$4937$4c368faf@roadrunner.com...
>>>> 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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote
>>>> in message news:%23Ued4ZghIHA.748@TK2MSFTNGP04.phx.gbl...
>>>>> 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
>>>>>
>>>>>
>>>>> "Unknown Soldier" <nomailplease.com> wrote in message
>>>>> news:47da981f$0$22818$4c368faf@roadrunner.com...
>>>>>>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
Tue Mar 18 12:53:59 CDT 2008

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" <nomailplease.com> wrote in message
news:47df37b3$0$17365$4c368faf@roadrunner.com...
>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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
> message news:%23zLeW4EiIHA.4536@TK2MSFTNGP06.phx.gbl...
>> 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" <nomailplease.com> wrote in message
>> news:47de9129$0$1117$4c368faf@roadrunner.com...
>>> 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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote
>>> in message news:eXo9efkhIHA.1944@TK2MSFTNGP02.phx.gbl...
>>>> 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" <nomailplease.com> wrote in message
>>>> news:47db33a0$0$4937$4c368faf@roadrunner.com...
>>>>> 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)" <rick.newsNO.SPAM@NO.SPAMverizon.net>
>>>>> wrote in message news:%23Ued4ZghIHA.748@TK2MSFTNGP04.phx.gbl...
>>>>>> 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
>>>>>>
>>>>>>
>>>>>> "Unknown Soldier" <nomailplease.com> wrote in message
>>>>>> news:47da981f$0$22818$4c368faf@roadrunner.com...
>>>>>>>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