Multiple IF statement - calculate fees excluding certain hours of the day

[nam24]

I have a data set that gives me all 'electric charge' transactions in a particular month. From the data, we can see when a charge is initiated, when a charge is complete, and when a charge is unplugged (ended). I am trying to figure out how much in penalties we would have accrued if we implemented idle fees at $2 an hour. All of my variables are at the top of the sheet.

Once the charge is complete, we would give people a variable, say 30 minutes, to unplug their charge. (So anything with less than 30 minutes of idle time does not incur a fee.) Here's where things get more difficult - we don't want people to pay a fee when their charge completes overnight. This "grace period" will be a variable, and we have it set from 10pm to 7am. Therefore, anything that's charge is completed and unplugged between these hours will not incur a fee. If the charge completes overnight, but they don't unplug until 10am, then they would incur 3 hours of idle time (10am-7am). However, if a charge completes anytime before 10pm, that person will be charged until they unplug.

Can someone help me with this? Here a sample of my workbook.

 

[nam24]

I think I found the spot:

(((C9-B9)*1440)-30)/E$4*$E$6

I think here we'd have to change 30 to point to the "idle_time_mins" variable, i.e. $E$4. Then change $E$4 to 60 so it looks like ...

(((C9-B9)*1440)-$E$4)/60*$E$6

 

[Candyman8019]

Yes, that's exactly it. I was calculating $2 per half hr rather than the full hr.

 

[Candyman8019]

Ah, there's the issue. I was doing $2 per half hr. Give this a try:

=IF(IF(AND(B9>=INT(B9)+$E$1,C9<=INT(B9)+1+$E$2),0,IF(B9<INT(B9)+$E$1,(((C9-B9)*1440)-30)/60*$E$6,(((C9-INT(B9)+1+$E$2)*1440)-30)/60*$E$6))<0,0,IF(AND(B9>=INT(B9)+$E$1,C9<=INT(B9)+1+$E$2),0,IF(B9<INT(B9)+$E$1,(((C9-B9)*1440)-30)/60*$E$6,(((C9-INT(B9)+1+$E$2)*1440)-30)/60*$E$6)))

As you mentioned above, you could use a variable for the 60 minute interval instead, which would be preferred and would allow easy modifications to your model down the road.