Should I use and or or

[Motoxboi26]

OK guys here is where I am getting hung up. A while back I created this scheduler for work, the original formula for totaling hours was not mine but I was able to modify it to suit my needs. I was asked to review this to see if there was a way that I could make hours calculate using either a 30 minute or 1 hour lunch period deduction depending on what was entered into that column ( Cells J40:48, T40:48, J98:106 and T98:106)

The formula I'm currently using was set up for either a 30 min lunch period or a 1 hour lunch period but I can't figure out how to make it calculate dependent on if a 3(30 minute) or a 1 (1 hour) is entered into the previously referenced cells. What's the best way to go about that. Just trying to make this more efficient for my front line teams so they have more scheduling options. Any help sent my way is always welcome.


The formula I'm using is

1 hour lunch break

=IF(R48="",0,IF(AND(T48=""),IF(P48<=R48,24*(R48-P48),24*(R48-P48)+12),IF(P48<=R48,24*(R48-P48)-1,24*(R48-P48)+12-1)))

30 minute lunch break

=IF(R99="",0,IF(AND(T99=""),IF(P99<=R99,24*(R99-P99),24*(R99-P99)+12),IF(P99<=R99,24*(R99-P99)-0.5,24*(R99-P99)+12-0.5)))

[TMS]

Instead of hard coding the 0.5 and 1 in the formulae, why don't you refer to a cell with the length of the break?