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.

 

[Candyman8019]

Give this a try in cell K9:
=IF(B9>=INT(B9)+$E$1,IF(C9-B9<$E$3,0,CEILING.MATH(((C9-B9)*1440)/E$4*$E$6,2)),CEILING.MATH(((C9-B9)*1440)/E$4*$E$6,2))

Assumption here is that idle fees are charged at $2 increments, ie if you're 35 minutes late you'll be charged 4.00. If this isn't the desired functionality, you can remove the ceiling.math which rounds up to the nearest $2.

 

[nam24]

Give this a try in cell K9:
=IF(B9>=INT(B9)+$E$1,IF(C9-B9<$E$3,0,CEILING.MATH(((C9-B9)*1440)/E$4*$E$6,2)),CEILING.MATH(((C9-B9)*1440)/E$4*$E$6,2))

Assumption here is that idle fees are charged at $2 increments, ie if you're 35 minutes late you'll be charged 4.00. If this isn't the desired functionality, you can remove the ceiling.math which rounds up to the nearest $2.

Thanks for responding. I'll try to plug this in later today and let you know the results. I forgot to mention that all fees will be prorated so I imagine I will remove ceiling.math.

 

[Candyman8019]

=IF(B9>=INT(B9)+$E$1,IF(C9-B9<$E$3,0,((C9-B9)*1440)/E$4*$E$6),((C9-B9)*1440)/E$4*$E$6) is what you'll end up with, without the rounding.

 

[nam24]

Something looks slightly off. For example in the first row of data, the charge completed at 8:57 and unplugged at 9:53pm and the formula returned 3.7. I think the formula is doubling what it should actually be because I forgot to clarify the 30 minute grace period (idle_time_mins variable). If people unplug within 30 minutes of their charge completing, they would incur no fees. Anything over 30 minutes would have that 30 minute grace period subtracted from the total idle hours. The first part can be accounted for by leading with this before proceeding with the rest of your formula.

=IF((C9-B9)*1440<=$E$4,0, ...

So in the example, idle time is 56ish minutes and the charge would be for 26 minutes (56 - grace period of 30 mins).

Also, in row 18 where someone was idle for days, it's not returning a fee. This would be one of the exceptions where it's initially done charging in the overnight window at 11:02pm. That person doesn't incur an idle fee until 7am rolls around. They should expect to receive a fee for the remaining duration of their idle time. Their fee would be $2/hr from 11/24 7am until they unplugged at 11/26 11:56am. This is the part that really tripped me up.

 

[Candyman8019]

Okay, this formula is getting long...if you have a newer version of Excel that can use the LET command we can simplify it, but otherwise 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)/E$4*$E$6,(((C9-INT(B9)+1+$E$2)*1440)-30)/E$4*$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)/E$4*$E$6,(((C9-INT(B9)+1+$E$2)*1440)-30)/E$4*$E$6)))

 

[nam24]

It made my head hurt as it got longer and longer, and that's ultimately where I got stuck. I think I came up with an alternate solution with the use of helper columns. What do you think about this? I can't think if I missed anything.
In column K, I created a new 'charge_complete_dt' that overwrites anything after 10pm or before 7am and assigns a new datetime to 7am the following day:

=IF(D9>=$F$1,DATE(YEAR(B9),MONTH(B9),DAY(B9))+1+$E$2,IF(D9<$F$2,DATE(YEAR(B9),MONTH(B9),DAY(B9))+$E$2,B9))

Column L is the new idle minutes calculation that zeroes out anything less than 30 minutes

=IF((C9-K9)*1440<=$E$4,0,((C9-K9)*1440)-$E$4)

Then in column M, it's the fee calculation:
=L9/60*$E$6

The time where fees start incurring is now technically 7:30am, but I'm ok with that.

 

[Candyman8019]

You could use helper columns...or string it all together. It gets ugly as one formula, but either way will work. Is there a specific scenario where my last formula didn't yield expected result?

If I understand your original post correctly, there are a couple of challenges to the logic you presented in your latest post:
- overwriting the charge complete date to the next day at 7am would fail if the charge was complete after midnight...so you'll need to take that scenario into account.
- Calculation fee would be based on 30minutes rather than 60minutes.

 

[Candyman8019]

If you have a newer version of Excel you can use the LET command to simplify the formula as follows:

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

which is really saying FEES is the calculation formula and the last section is the evaluation of if fees is less than 0 then 0 else provide the calculation result.

otherwise using the long formula here's a breakdown:
=IF(IF(AND(B9>=INT(B9)+$E$1,C9<=INT(B9)+1+$E$2),0 ‘if charge complete is >10pm and <7am next morning then 0…no charge since it completed over night.
‘ else
,IF(B9<INT(B9)+$E$1,(((C9-B9)*1440)-30)/E$4*$E$6, ‘if charge complete is prior to 10pm then calculate fees based on unplug time minus charge complete time minus 30 minutes * $2.00
‘else
(((C9-INT(B9)+1+$E$2)*1440)-30)/E$4*$E$6))<0 ‘calculate charge from 7am following the charge complete date until unplug time -30minutes * $2.00
‘the remainder of the formula is simply a duplicate for the IF statement that says if the total charge is negative then 0. This situation would arise if the difference between the charge complete time and the unplug time was less than 30 minutes.
,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)/E$4*$E$6,(((C9-INT(B9)+1+$E$2)*1440)-30)/E$4*$E$6)))

 

[nam24]

Unfortunately, I don't have a version that's compatible with LET so stuck with the long formula haha.

I noticed some of the results were overstating things a bit so I think the prorate factor needs adjusted. For example in row 10 (stations T18), idle time is roughly 1 hour and 39 minutes or 98.97 total minutes. After we take away the 30 minute grace period, the new idle time that gets the fee is 68.97 minutes. At $2/hr of idle time, the fee should be around $2.30. I got double that with the long formula.