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

nam24

New Member
Joined
Nov 11, 2014
Messages
13
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.

charges example.jpg
 
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
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Yes, that's exactly it. I was calculating $2 per half hr rather than the full hr.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top