Calculate February Correctly for Monthly Allocation

Good morning! I apologize for the delay in getting back with you. We had severe storms and rolling power outages.

Thank you for replying back to my post so quickly. I was able to test and tweak the string as I was still coming up with a mix of True/False in Column Z. The following formula appears to work 99% of the time throughout the 8 programs I have tested it on:

=IF(AND($D32<=M$30,$F32>=M$31),$K32*30,IF(AND($D32>=M$30,$F32<=M$31),(MIN(DAY($F32),30)-DAY($D32)+1)*$K32,IF(EOMONTH($F32,0)=M$31,(MIN(DAY($F32),30)+0)*$K32,IF(EOMONTH($D32,0)=M$31,(31-DAY($D32))*$K32,0))))

There are 25+ programs that are being where this is being applied to so I will have to continue to test and develop it!

I say 99% because there is one instance where it was off by $0.01. However, this is likely due to a rounding issue as the amount we bill does not break down evenly. The other instance was when it it did what it was supposed to and calculated February as a 30-day month. I believe this discovered a billing issue on our end where only 28 days was billed when it should have been billed for 30.

Thank you again, I hope you're well, and thank you for welcoming me to the forum!

Thank you for the reply. I will test this a little bit later today and get back with you! I am always looking for more than one way to complete something.

Originally Posted by kvsrinivasamurthy

In G3 copy to whole range.

Formula:

Please Login or Register  to view this content.

Good evening! Thank you for this. I applied it to a number of ranges and didn't have any issues, except when one of the programs started on March 1. It calculated two days for February. I consider this another 99% solution and I'm greatly appreciative of you taking the time to look it over!

Thank you again!

@ Sparky156: Here is a compact formula that handles almost every entry in the sample file as expected.
The only exception is Line 12 (4-Feb-2023 to 28-Feb-2023). The formula treats it as a 27-day period.
This seems logical to me since 28-Feb-2023 is the last day of the month, which is considered a 30-day month.
If you need the last day of February handled differently, the formula can be further tweaked.

=$E3*MAX(DAYS360(MAX($A3,EOMONTH(G$2,-1)+1),MIN($B3,EOMONTH(G$2,0))+1,0),0)

BONUS: you don’t have to change the end date of each month in Row 2 to 30 for those months ending in 31.

@ kvsrinivasamurthy: your formula will mishandle 29-Feb.