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.
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
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.
Luke, I've got an idea on how to solve this but want to first check to see if you already have a solution.
Bookmarks