Results 1 to 3 of 3

Thread: help with nested if than statement

  1. #1

    help with nested if than statement



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello, I was elected by my employer to write a formula for a "line bar" scheduling program that we use because I have some experience with Excel. I have been trying to figure this formula out for a LONG time and cannot wrap my head around it. Any help would be appreciated.
    Here is what I have I have a spreadsheet that breaks down the work day hour by hour. I need it to display how much time elapses between the two cells (for example if an employee comes in at 7:30 am and works until 2 pm than it needs to show a 0.5 in the first box, than a 1 in each box until the 2 pm box). I have posted the spreadsheet so far:
    Monday
    6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00 0:00 1:00 2:00
    Projected sales 0 0 0 0
    Labor guide 34 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4
    Scheduled 8
    Guide +/- -49.5
    Name In Out 6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00 0:00 1:00 2:00 3:00
    employee a 6:30 14:00 0.5 1 1 1 1 1 1 1
    employee b 14:00 23:00 PM 1 1 1 1 1 1 1 1 1
    employee c 14:00 2:00 AM
    employee d 15:00 cl



    The problem that I am having is that I have a formula that only partially works:

    =IF(OR($B9>=H$8,$C9<=G$8),"",IF(AND($B9<=G$8,$C9>=H$8),1/24,IF(AND($B9>G$8,$C9>H$8),H$8-$B9,$C9-MAX($B9, G$8))))

    It does not work for times midnight until 3 am. I played around with a MOD formula but still cannot get anything to work.
    I also need to include in that formula that if "cl" (close) is entered in the out time it references a cell to get the closing time of the store to do the calculations

    Please help

    thanks,

    Lucas
    Attached Files Attached Files

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Crosspost at http://www.mrexcel.com/forum/excel-q...ml#post3504013

    Luke, please post at ONE forum only. Otherwise a volunteer's time gets wasted in the case that it's been answered on one forum but they are not aware.

  3. #3
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Luke, I've got an idea on how to solve this but want to first check to see if you already have a solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •