# Thread: help with nested if than statement

1. ## help with nested if than statement

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

thanks,

Lucas

2. 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. 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
•