Calculate meal periods based on total scheduled hours

Hey fellow excelheads,

I've been asked to update an excel based scheduler tool I created a few years ago. I've done some reformatting but I am having formula trouble trying to get total shift times to calculate with meal periods included based on total hours scheduled. Since I got great help here the last time I was having troubles I'm back because you all rock!

Per our policy an employee will have a 1/2 hour meal period for a shift that is scheduled more than 5 hours but less than 10 hours and a 1 hour meal period for schedules that are 10 hours or longer.

So what I was doing originally in rows 39-47 and 99-107 was running the calculations to deduct at 1/2 hour meal break if an X was placed in k39:k47 , U39:u47 , K99:K107, and U99:U107 with one row set for a 1 hour. This worked ok but a number of our locations are now running non-traditional schedules so this isn't working as well as it could be for calculating and showing total scheduled work hours vs physical hours for managing budgets.

What I want this to do is have TTL Hours in columns J and T deduct the appropriate meal period based on the total hours scheduled and then place a 1 or a 2 in the corresponding K or U Column to reflect the number of meal periods.

so J39 is my target cell this is the current formula =IF(I39="",0,IF(AND(K39=""),IF(G39<=I39,24*(I39-G39),24*(I39-G39)+12),IF(G39<=I39,24*(I39-G39)-0.5,24*(I39-G39)+12-0.5)))

I need this to subtract .5 if the total is greater than or equal to 5 and less than 9.9( we only deal in quarter hour scheduling but I want a buffer in case someone does something unexpected) and subtract 1 if the total is equal to or greater than 10 ...I would love if it could also put a 1 or a 2 in K39 that would show if 1 meal period (.5 hours) or 2 meal periods (1 hour) was being deducted. This will show the person scheduling the total "work hours" that are scheduled not the physical number of hours that has been scheduled.

Who's got some ideas because my brain is fried like an egg.

I've changed my thinking on this and am going a different direction with my forumlations....i figured out how to simplify things ALOT...thanks for looking everyone

Hopefully I can explain what I did here

I moved and simplified the original formula that had the "hardcoded" breaks into the X column so that it would just calculate total hours scheduled =IF(I40="",0,IF(G40<=I40,24*(I40-G40),24*(I40-G40)+12))

Then in the total hours box I put in this formula =IF($X40>=10,$X40-1,IF($X40>=5,$X40-0.5,$X40-0)) to designate the proper meal periods based on the total hours in X40

I abandoned the need to input the 1 or 2 for number of meal periods as it was an un-needed redundancy

I then carried the formulas to all related cells in my schedule array.