Help, Excel logical arguments

Discussion in 'Off Topic [BG]' started by SpankBass, Jan 11, 2004.

  1. I am trying to make it so the schedule at my work (done in excel) will check the times a person is sceduled against the master availability. That way, if someone is mistakenly scheduled at a time when they are not available, a notice saying "Unavail" will appear.

    I believe I have the correct formulas to do this, but it is not working. I was hoping that one of you who may know more than me about Excel can give me a hand.

    Here is the formula in the cell:
    =IF(C8=0,0,IF(D8=0,0,IF(C8<'[Test 2.xls]Managers'!$C$8,"Unavail",IF(AND(D8>'[Test 2.xls]Managers'!$D$8,'[Test 2.xls]Managers'!$D$8<>"Close"),"Unavail",IF(D8>C8,(IF((24*(D8-C8))>=5.5,(24*(D8-C8))-0.5,(24*(D8-C8)))),IF(D8<C8,(IF((24*((D8+1)-C8))>=5.5,(24*((D8+1)-C8))-0.5,24*((D8+1)-C8))),""))))))

    The formula as a whole states: If C8 (start time) or D8 (end time) is = to 0, then the cell will come up as 0. If not, then if C8 is less than the opening time for this person on the Master Availability sheet (I saved that sheet as "Test 2) the cell will come up as "Unavail." If not then if D8 is greater than the closing time for on the master availability, AND the master availabilty sheet does not say "Close", then the cell will come up as "Unavail." (I have to put in that "Close" part, as we are often scheduled to 1:30AM, I can't just have the greater than part because if someone is sceduled to 5:00pm, but is available till 2:00 AM, the cell will come out as unavail because 5:00pm is greater than 2:00 AM) If not, then the rest of that basically figures out the amount of hours in decimal form minus a half hour break if the shift is more than 5 and a half hours.

    So my problem area is the area bolded. There is something wrong with that formula that I am not seeing.

    I've attached the two files, Since it is an actual schedule, I deleted everyone's name, to protect the innocent or something. I have only tested the formula on the Manager tab for the "Salary Form" (which does not calculate out a break) and for "Joe Shmoe" but only for Wed the 31st.

    If someone could give some advice that would be awesome.
  2. if i were you i would forward your post to a usenet.
  3. Surely we have SOME nerds on this forum!

  4. wulf


    Apr 11, 2002
    Oxford, UK
    The Windows machine I have with Excel on is currently suffering from a dodgy hard drive and so I'm only using it for absolutely essential tasks (running the IIS portion of my web development servers NOT hacking other people's Excel problems).

    However, I can tell you exactly what I would do if trying to troubleshoot it and that's to break it down one section at a time. You've got a lot of nested IF statements and I would test each one individually first to make sure it is giving the expected results before combining them all together.

    Divide and conquer...

  5. MJ5150

    MJ5150 Terrific Twister

    Apr 12, 2001
    Lacey, WA
    I'm with Josh on this one....I will download you files, and check it out tonight....I am hot on the trail for a PDA, Bassballs, 115 or 118, and a snowboard. But, I will check it out later.

  6. I'm definitely a nerd, but I've only barely used Excel.

    I'll download your spreadsheets and give it a look tonight anyway...I'll give it a shot.
  7. I'm sorry, re-reading my post I realize that I wasn't too clear.

    Ok, take a look at my spreadsheet called "Test," this is the spread sheet we use for the weekly work schedule. Goto the Cell "C9," that is where you will see the code for what I am trying to accomplish. What I am trying to do, is make that cell come up saying "Unavail" should someone be scheduled at a time that is out of their range on the master availability "test 2."

    Take a look at the master availability for Joe Schmoe, you'll see he is only available 9-5 on wednsdays. Go back to "test" and try to schedule him at 8:00 AM on Wednsday. You'll see that the cell comes up saying "Unavail."

    I know that that part of the code works, it's the second part that I put in bold letters in my first post that I am having problems with. Originally, I just had the simple IF situation

    IF(D8>'[Test 2.xls]Managers'!$D$8),"Unavail", (moves on to the rest of the formula calculating the hours minus the breaks)

    That basically says If the info in D8 (end of shift time) is greater than the cell D8 on the master availability, then the cell would say "Unavail." If it was not greater,then it would move onto the rest of the formula.

    The problem with that was that management is scheduled until 1:30 AM usually. If say someone is available open til close (I'd write it in the master availability as 6:00AM to 3:00AM) and they were scheduled 9:00am til 5:00pm, the cell would come up saying "Unavail" because 5:00pm is greater than 3:00AM.

    Ya follow?

    I couldn't figure out a way around that, so I tried doing this:

    IF(AND(D8>'[Test 2.xls]Managers'!$D$8,'[Test 2.xls]Managers'!$D$8<>"Close"),"Unavail",(moves on)

    That basically says If D8 is greater than the D8 on the master availability AND it does not say "Close" on D8 in the master availability, then "Unavail" if not the it moves on.

    So basically whenever someone is available til "close," I would just write the text "close" on the master availability. I think that should eliminate the problem, but its not. If I tried to schedule, say Joe Shmoe til 1:30 AM on wednesday (note that he is only available til 5:00pm) it doesn't come up as "unavail." THAT is where I am having my problems.

    I don't know if I am just makeing a small mistake somewhere in the code, or if my logic is flawed and I should try something else. That's why I posted it here because I know no one who knows how to do anything with Excel, sans a few adding and subtracting formulas.

    I hope that explains everything, just ask again if you have any more questions. Oh an don't worry about not getting to this any time soon, I have no deadline or anything. I would like to try and get it done by next Saturday so we can try it out when the new schedule needs to be made, but like said, no hurry.
  8. wulf


    Apr 11, 2002
    Oxford, UK
    If you're mixing text ('close') and times ('5:00') it may be that Excel is getting confused about comparing them - to track that down, you need to break your nested IF statement into chunks and check that each one responds as expected for a range of inputs.

    One way round that is to enter date and time in the same cell, eg. '13/01/2004 8:00'. If you format that as a number, it comes out as '37999.333' - the integer represents the date and the decimal fraction represents the time (8am is 1/3 of the way through the day, hence .3 recurring).

    That means you can use the numbers to provide accurate comparisons. For example, '14/01/2004 7:00' is held in Excel's memory as '38000.292'. Therefore, while 7am (.292) is less than 8am (.333), 38000.292 is greater than 37999.333 which would allow you to stick to greater than and less than comparisons and probably simplify your function.

    Get to grips with this and not only will you be able to figure the problem out yourself but you'll also be able to be more confident that it is working reliably in future. Teach a man to fish... :D

  9. jazzbo


    Aug 25, 2000
    San Francisco, CA
    I think the juxtaposition of your parameters might be superimposed by the paradigm imbedded in the first vector.

    Try cross-referencing your two column segments with the second bilateral spiral sigma flex capicitor. Get the thing up to 88mph and subdivide with the following formula:

    (3xb/2ac)*43, :) :D ;)

    ... where "A" = 69.

    This should work.

    Oh yeah, set the options to 1.5.
  10. MJ5150

    MJ5150 Terrific Twister

    Apr 12, 2001
    Lacey, WA
    DOH!! I forgot all about this. I am so obsessed right now with buying a PDA, snowboard, and 115 that it is consuming me!

    Sorry bro. I will check it out in the AM.

  11. I tried this and managed to travel 6 minutes into the furture.
  12. I've thought about this idea before, but there is a problem. I would need to enter the dates onto the master availability sheet, and I would need to update these dates every week when the schedule is being done. We have 50+ employees, so this just doesn't sound worth it.

    Now if there were a way to make a formula so that it would get the date from another cell, AND still keep the time that we originally put it, then it would work. There may be a way to do this, but I am not sure how as I am still very new to the program.

    You know, something like:
    =(cell with the next days date)+3:00 AM

    Of course that doesn't work, but you get my point, right?

    Thanks for any help.
  13. wulf


    Apr 11, 2002
    Oxford, UK
    When you type in the times, how does Excel store them? Change the formatting option to number to get an idea what's going on.

    If you get a result like 0.5 for 12 noon or 0.75 for 6pm, then you can combine that with the date value from another cell to get a date time value. You could either set up, for example, cell C1 to equal A1 + B1 or just include the sum in your formula - IF((A1+B1), True action, False action).

    That might be easier to enter but still means you're working with date/time values the spreadsheet can understand.

  14. jazzbo


    Aug 25, 2000
    San Francisco, CA
    Scary then, isn't it?
  15. nah, 6 minutes isn't that big of deal.
  16. There we go, that worked! Thank you very much.

    Now I need to figure out how to do the request off shifts...
  17. wulf


    Apr 11, 2002
    Oxford, UK
    How does that work? It always helps to think about the process and try to understand that before you put your fingers on the keys to come up with a computer assisted system.

  18. All ready figured it out. :)

    Now the only thing left to do is copy and paste the code onto the real thing (making a back-up file first, of course) and giving it a test run when the schedule is done this Saturday.

    I did notice that writing out on a piece of paper what I wanted to do and what my code is actually saying helped alot.

    Thanks alot for your help wulf. ;)