 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008If 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 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 > I have two sheets in a workbook. One called Availability, and the other > called schedule. The availability tells what time my employees are [quoted text clipped - 32 lines] > What formulas do I put in cells of Column D, E, F the get the deserve effect > from above? Thanks It has a reference problem after I did that > Paste this into cell D1 on the schedule sheet. And drag down column. > =IF(AND(Availability!B1>B1,Availability!C1>C1),"Only available [quoted text clipped - 63 lines] >> effect >> from above? Thanks Make sure the formula has correct spelling of the sheet names > It has a reference problem after I did that > [quoted text clipped - 65 lines] > >> effect > >> from above? Thanks 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 >I have two sheets in a workbook. One called Availability, and the other >called schedule. The availability tells what time my employees are [quoted text clipped - 32 lines] > What formulas do I put in cells of Column D, E, F the get the deserve > effect from above? Thanks 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 > Give this a try... > [quoted text clipped - 59 lines] >> What formulas do I put in cells of Column D, E, F the get the deserve >> effect from above? Thanks 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 > Give this a try... > [quoted text clipped - 59 lines] >> What formulas do I put in cells of Column D, E, F the get the deserve >> effect from above? Thanks 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 > It does not seem to work right. > [quoted text clipped - 79 lines] >>> What formulas do I put in cells of Column D, E, F the get the deserve >>> effect from above? Thanks 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 > 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 [quoted text clipped - 91 lines] >>>> What formulas do I put in cells of Column D, E, F the get the deserve >>>> effect from above? Thanks 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 > Thanks so much Rick > [quoted text clipped - 108 lines] >>>>> What formulas do I put in cells of Column D, E, F the get the deserve >>>>> effect from above? Thanks 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. > Your initial post mentioned nothing about "days"... just 3 columns. For > future reference when asking questions on newsgroups... don't simply your [quoted text clipped - 120 lines] >>>>>> What formulas do I put in cells of Column D, E, F the get the deserve >>>>>> effect from above? Thanks 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 >I apology, I did not know more problems arrive as more needs come to mind >for the sheets I need. However, here it is. [quoted text clipped - 163 lines] >>>>>>> What formulas do I put in cells of Column D, E, F the get the >>>>>>> deserve effect from above? Thanks 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. > Okay, I see another change from your original post (which, I think, will > also affect how I restructure my original formulas). In your original [quoted text clipped - 183 lines] >>>>>>>> What formulas do I put in cells of Column D, E, F the get the >>>>>>>> deserve effect from above? Thanks 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),"") > It does not seem to work right. > [quoted text clipped - 79 lines] > >> What formulas do I put in cells of Column D, E, F the get the deserve > >> effect from above? Thanks 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 > I have two sheets in a workbook. One called Availability, and the other > called schedule. The availability tells what time my employees are [quoted text clipped - 32 lines] > What formulas do I put in cells of Column D, E, F the get the deserve effect > from above? Thanks
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.