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 


0
Unknown
3/14/2008 3:22:10 PM
excel.newusers 15348 articles. 2 followers. Follow

14 Replies
502 Views

Similar Articles

[PageSpeed] 5

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 
> 
> 
> 
0
Mike1154 (1216)
3/14/2008 4:24:01 PM
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
>>
>>
>> 


0
Unknown
3/14/2008 5:25:10 PM
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
> >>
> >>
> >> 
> 
> 
> 
0
Mike1154 (1216)
3/14/2008 5:55:01 PM
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
> 

0
3/14/2008 7:06:30 PM
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
>>
> 

0
3/14/2008 7:12:37 PM
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 
> 
> 
> 
0
3/14/2008 7:13:01 PM
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
>>
> 


0
Unknown
3/15/2008 2:25:41 AM
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
>>>
>>
>
> 

0
3/15/2008 2:54:41 AM
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
> >>
> > 
> 
> 
> 
0
3/15/2008 2:27:01 PM
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
>>>>
>>>
>>
>>
> 


0
Unknown
3/17/2008 3:41:41 PM
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
>>>>>
>>>>
>>>
>>>
>>
>
> 

0
3/17/2008 4:43:56 PM
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
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
> 


0
Unknown
3/18/2008 3:32:16 AM
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 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
>>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
> 

0
3/18/2008 5:53:59 PM
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.

"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in 
message news:ubNfHESiIHA.944@TK2MSFTNGP05.phx.gbl...
> 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 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
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
> 


0
Unknown
3/18/2008 10:58:49 PM
Reply:

Similar Artilces:

IRERR nested formula help please
hi just wondering if someone can help with the following nested ISERR formula. I want to be able to show in J3 that if the value in G3 is "0" then show as "-100%" OR if the value is "NULL" then show as "0" but if the value is >0 then I3/G3*100 it is working to a degree but if value in G3 is NULL then still shows as -100% I want it to show as 0% if NULL and -100% if 0 - hope it makes sense.... currently have following in J3 =IF(ISERR(I3/G3),-100,I3/G3*100) G3 = Order I3 = Profit thanks in advance Depending on how I interpret NULL =IF(ISBLANK(G3)...

Internationalization for formulas & macros
Hi all, I have a relative who send me some Excel spreadsheet (97, 2000 or XP that contains formulas and macros written in english. However, I have a french version of Excel. Because of this, I can' open these spreadsheets. My question is quite simple. Is there a tool or an easy way to convert all formulas & macros withi a spreadsheet from english to french? Any help will be appreciated. Thank' -- micto ----------------------------------------------------------------------- mictou's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1637 View this th...

counta formula
I have the formula =10/counta(E6:E15 ). This gives me 10/10 if E6 through E15 are filled. How do I make this formula: =10/counta( E6:E15 but exclude the cells from this range that equal any of the numbers in B5:B8) Example: =10/counta(E6:E15) would be 10/10 if E6:E15 are filled but if E12 = B5, excel would give me 10/9 because E12 would be excluded This is an array formula, confirm it by pressing CTRL-SHIFT-ENTER: =10/COUNTIF(E6:E15, "<>" & B5:B8) Does that help? -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScien...

Creating a Formula to Format Column automatically? #4
Does it matter what order the conditions are in? Some of them worked some didn't -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/showthread.php?threadid=26434 If you have 3 conditions, the order does not matter. What matters is the content of the cell. Not case-sensitive, but do you have any extra spaces if text? If looking for numbers, are they real numbers or text that looks like numbers? You used Joe Blow and...

Subtotal
OK, so I have a list of orders. Some orders have just one row of data, some have two rows of data, some have three, some have four, etc. Each row represents either a delay or an activity. (So, obviously, an order can have more than one of either.) I need to aggregate into one row: Order | Type of Order | Num. of Delays | Total Length of Delays HERE'S THE MANUAL WAY I'M DOING THIS NOW: ====================================== My initial columns are: Order Number (A) | Type of Order (B) | Action Code (C) | Action Length (D) I added a "marker column" - Dela...

formula fill not working correctly in 2007
If I use the fill handle to copy a formula, the formulas are well copied, but the results are not. Exemple: The formula in A3 is =A1+A2. If I copy the formula to B3 it reads =B1+B2, which is correct. The result given in B3 however is not B1+B2 but the same result as in A3. I have to recalculate the formule in B3 for the correct result. In previous versions Excel did this automatically. How can I fix this problem or is it a bug? Hi It sounds as though calculation is set to Manual. Tools>Options>Calculation>and select Automatic -- Regards Roger Govier "Jante" <J...

Formula to sum values extracted from string
Hi all I'll preface this by pointing out that I do NOT want to use text to columns, nor a VBA solution. I'm looking for a formula alternative - maybe an array formula. I have a single column of data extracted from a string, which represents numeric values, single space-separated. The data looks like this: 8.56 2,514.12 3.18 0.35 What I'd ideally like is a formula that sums each of the four values, which in the above example would be 2,526.21. Any suggestions appreciated. >8.56 2,514.12 3.18 0.35 Assume that string is in cell A1. Create this named...

Excel Formula depending on number in cell..
Hello everyone! I have a question that I thought some of you could answer: I have a number in column D and based on if it is $0-$100 I want i multiplied by 0.10 and then rounded up to .95. So, if it is 20.49 + 10% = $22.54 and then rounded up to $22.95 Now this is where it gets tricky.. If it is between 100.01 to 200.00 + 7% and then rounded up to a .95. So for example 129.44 + 7% = $9.08 = $138.50 then rounded up to .95 $138.95. I need to do this for a range of numbers like 200.00 to 300.00 an 300.00 to 500.00 etc, etc.. Could someone point me in the right direction? I appreciate a...

formula within same cell
I don't have to much experience in Excel but I need to find out how to run a formula in a specific cell, for example if I enter 100.00 in A1 then come back and want to enter 250.00 then press enter can I get it to answer 350.00? Mike To show 350 as the answer, type the following, followed by enter =100+250 If you will be doing a lot of this, you might consider entering 100 in cell A1, 250 in cell A2 and =SUM(A1:A3) in cell A4 (leaving cell A3 for an additional value). Stephen http://groups.msn.com/HighlandsVillage where Villagers help each other "Mike" wrote: > I don...

VLOOKUP and IF statements
Hi there, Once again, I would like to tap the infinite knowledge of Excel out there. I am using a VLOOKUP function to bring in dates from another worksheet. I want my dates to end up in the 8/04/04 format. The trouble is that because I am using VLOOKUP with a sheet with incomplete data, some values are zero, and others are #N/A. For example, I am trying to pull in the dates that a particular product has sold. If a product had not sold, its 'date' field would be blank, and VLOOKUP returns a zero value. If it's not in the table at all, VLOOKUP returns a #N/A value. To combat...

formula bar #7
I'm calculating simple percentages. I enter 5/10*100 and 50 appears in the cell. When I go back to that cell, what shows up in the formula bar is ..5*100. This makes it impossible for me to check to make sure I entered the correct numbers to begin with. Is there a way to make it keep the original formula in the formula bar when you return to the cell? Peter, If the cell is formatted for Percentage then enter =5/10 (equals fiv divide by 10) to produce 50% in the cell and =5/10 in the formula bar. The formula you are using suggests the cell is formatted to Number, bu if you enter =5/...

Formulas with a variable # of worksheets
I am building a workbook that is intended to capture information on new feature requests for a system. The workbook will then consolidate information from each new feature request and perform different kinds of analysis to facilitate portfolio management (ranking views of each feature to make a determination as to which features will be funded for the next release). Analysis (and input for each feature) will include things like value to the customer, value to the company, development cost, marketing risk, development risk, deployment/support risk, company strategy supported, etc. I think I ...

formula in a pivot table?
Hello guys, is that true that excel-pivot is only able to sum (meanvalue is no selectable) from a formula made table. usually you put by drag and dro the data field into your table. but you also can put by hand a formula which is quit useful sometimes. after lunching the data-field in your pivot you can say pleasy show me the minimum or the meanvalue. It looks like that is not possible when you create a data-field using formula. then excel is just able to sum that ****... do you have any other idea?? cheers, filipu -- filipu ------------------------------------------------------------...

sql statement #3
I want to update the the qty and the retail price for All the items in the database from a file (xl or csv). I know the SELECT,UPDATE,WHERE statements. But how do i link from a file. Any detail help will be appriciated. thanks Have you thought about using the Inventory Wizards instead of scripting your changes? The wizards are designed specifically for projects like this. -- Jocelyn "faiz" wrote: > I want to update the the qty and the retail price for All the items in the > database from a file (xl or csv). I know the SELECT,UPDATE,WHERE statements. > But how do i ...

How do I correct formula display errors?
I use Word 2003, and recently formulas created by my professor do not display properly on my laptop. For instance, the ampersand symbol seems to appear where some superscripting or ' characters should be. What is the problem and solution? Hello Brian brian wrote: > I use Word 2003, and recently formulas created by my professor do not display > properly on my laptop. For instance, the ampersand symbol seems to appear > where some superscripting or ' characters should be. What is the problem and > solution? what version of Word has your professor been...

IF formula & COLOURED CELL
I would like to use a formula if A1 (e.g.$15) is filled in yellow then A1*2%, if in green then A1*1%.....Is there any way to do that. TIA. -- littleps ------------------------------------------------------------------------ littleps's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25482 View this thread: http://www.excelforum.com/showthread.php?threadid=391785 Hi, Check out this site: 'OzGrid' (http://www.ozgrid.com/VBA/ReturnCellColor.htm) Regards, -- aristotl ----------------------------------------------------------------------- aristotle...

Array Formulae Problem
I have the following setup A B C 1 Job Priority Satus 2 1 1 COMPLETED 3 2 1 CANCELLED 4 3 2 CANCELLED 5 4 3 WIP 6 5 1 WIP 7 6 4 WIP 8 7 1 COMPLETED I am trying to calculate the number of priority 1 jobs that have been completed and cancelled (the answer should be 4). I have tried looking at Array formulas, but I have not been able to figure them out. I would be grateful if anyone could offer any pointers. Best regards Neil =SUMPRODUCT((B2:B100=1)*(C2:C100={"Completed","Cancelled"})) I make it 4 -- HTH RP (remove nothere from the email address if mailing direct...

Changing a Parameter Of A Formula
Hi, I would be grateful if someone could help me with the problem have got. I have written a macro, with my user defined functions. One of my user defined functions is called GetData and it has two parameters Reference and Last_Value, eg GetData(Reference, Last_Value). I call GetData by assigning this to a cell like so (which works fine): Cell.Formula = GetData(Reference, Last_Value) Now within the GetData function I use the Reference Parameter to get a New Value, which will be returned to the cell (which works fine too). But what I wanted to know is how can I change the Last_Value ...

counta formula
I want to divide a number by counta for certain cells (which would yield a number). For example, =10/counta(A1:A4) would equal 10/4 if cells A1 to A4 are filled. What I am stuck on is this: =10/counta(A1:A4 but exclude the cells from this range that equal any of the numbers in in the range B5:B8) Example: =10/counta(A1:A4) would be 10/4 but since A2 = B7, the formula gives me 10/3 because A2 is excluded from counta What should happen if all the numbers match? Try something like this: =10/SUMPRODUCT(--(ISNA(MATCH(A1:A4,B5:B8,0))),A1:A4) -- Biff Microsoft Exce...

convert text to formula using VLookup
My question; I've seen somewhere that you can convert a text string into a formula using the INDIRECT Function. What I am constructing is a maintenance Schedule where some items need to be serviced on a weekly basis, others monthly, quarterly, bi-annually, and yearly. (Those are the only five rotations I require at the moment.) Once constructed the format will take on the appearance of a calendar with conditional formatting to highlight the dates the service falls on. Each of the rotations require (I think) different formulas, so what I want to do is have a list of the f...

Help with Formula Scenarios from yesterday
Repost with responses Hi AlfD Well, here's the problem, all of the cells which are being averaged contain formulas which until other cells are filled weekly, will contain zeros. So, I wrote those to ignore the zeros. I've instructed the person who will be populating the weekly, currently empty cells to use "n/a" or "---" to show that the cell has been addressed but will not contain a value, so I'm in a quandry. I think it's best to ignore the zeros, but what's making my averages come up wrong. So, I think leave that condition alone. What I need, ...

Formula which includes new sheets too?
Hi all, I don't know if this is possible, but any suggestions would be much appreciated! I have a spreadsheet with a few sheets, 1 for each site and then a Summary sheet which displays totals. eg. Site 1 shows how much has been fee'd for that site in each month, and the Summary Sheet shows how much has been fee'd for all sites in that month, by using a formula which takes the value's from each sheet. Is there a way of editing the formula so it will automatically include cell H3 for any sheet, including any new sheets added, without me having to amend the formulas? T...

Reference a cell value in a formula
I have the following formula: =SUMPRODUCT((MONTH(Summary!$F$7:$F$15000)=1)*1) I want the "15000" to be replaced by the varaible value of a cell in an other worksheet ("Summary"). I tried using INDIRECT but it didn't work. Thanks in advance =SUMPRODUCT((MONTH(OFFSET(Summary!$E$7,0,0,C4,1))=1)*1) C4 in this case is the cell that holds the number of items to be used in the calculation -- If the post is helpful, please consider donating something to an animal charity on my behalf ..... and click Yes "Leon" wrote: > I have the f...

cell variables in IF formula
i have this formula (Thanks Biff) =IF(J13="","",IF(J13=0,"Stock not rated",IF(OR(J13={1,2}),"Sell",""))) now i want to add in further variable - if J13 has "cmt" or "divs" then the cell is blank, then if any of the above... thank you =IF(OR(J13={"","cmt","divs"}),"",IF(J13=0,"Stock not rated",IF(OR(J13={1,2}),"Sell",""))) -- Return email address is not as DEEP as it appears "Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wro...

Help: Macro that runs various if statements, inserting rows etc
I am a newcomer to Macros and excel programming and was hoping someone could assist / provide the code to do the following: I have a +50,000 line spreadsheet, that needs to be formatted. Given the size I would like to automate this. There are 3 key formatting changes required and detailed below: n = 1 1. If Cell A(n) not equal Cell A(n+1) then insert a new row below A(n), make Cell F(insertedrow)=1, make Cell G(insertedrow)=A(n+1), make Cell H(insertedrow)=B(n+1) 2. If Cell C(n) not equal Cell C(n+1) then insert a new row below C(n), make Cell F(insertedrow)=2, make Cell G(inserte...