Put the if statement on column D instead of column E because that is what is really changing, right? Also, time is always a little funky in excel if you don't use it right. So put the following in each column
A:
=time(hour,minute,second) for your example this would would =time(8,0,0)
Then in B do the same thing:
=time(18,0,0)
Column C will be the same =B-C
Column D has your if statement that will look like this:
=IF(C1=TIME(12,0,0),TIME(1,0,0),TIME(0,30,0))
That is saying that if the shift is 12 hours or longer report back a break of 1 hour otherwise report back a 30 min break. Last is your total billable hours in column E:
=C-D
Does that anwer your question?
|