CODEWITHSUNDEEP
excelexcel-formula
spacej3di
spacej3di

Reputation: 68

Calculate Monthly Amount Using Daily Rates and Start/End + Rate Change Dates

O365 Excel 2305

data

Table to copy/paste in B2

reg2 start 4/14/2024 Regular
special2 start 1/1/2024 Special
start date end date type daily1 daily2 7/31/2023 8/31/2023 9/30/2023 10/31/2023 11/30/2023 12/31/2023 1/31/2024 2/29/2024 3/31/2024 4/30/2024 5/31/2024 6/30/2024
9/12/2023 6/30/2024 Regular 100 120
7/1/2023 3/3/2024 Regular 100 120
11/14/2023 6/30/2024 Special 80 110
7/1/2023 10/5/2023 Special 80 110

G7 formula below:

=IF($D7=$G$2,
((MAX(G$6-$B7,0)-MAX(EOMONTH(G$6,-1)-$B7,0))
-(MAX(G$6-$E$2,0)-MAX(EOMONTH(G$6,-1)-$E$2,0))
+(G$6=$B7))*$E7
+((MAX(G$6-$E$2,0)-MAX(EOMONTH(G$6,-1)-$E$2,0))
-(MAX(G$6-$C7,0)-MAX(EOMONTH(G$6,-1)-$C7,0))
+(G$6=$B7))*$F7,
((MAX(G$6-$B7,0)-MAX(EOMONTH(G$6,-1)-$B7,0))
-(MAX(G$6-$E$3,0)-MAX(EOMONTH(G$6,-1)-$E$3,0))
+(G$6=$B7))*$E7
+((MAX(G$6-$E$3,0)-MAX(EOMONTH(G$6,-1)-$E$3,0))
-(MAX(G$6-$C7,0)-MAX(EOMONTH(G$6,-1)-$C7,0))
+(G$6=$B7))*$F7)

G7 would calculate:

Days at Rate1 * DailyRate1 + Days at Rate2 * DailyRate2

Rate2 starts on a different date for "Regular" Type vs "Special" Type

Struggling to incorporate the End Date (as seen by negative amounts).

Any insight is appreciated.

Upvotes: 0

Views: 173

Answers (2)

DMM
DMM

Reputation: 1112

This formula for cell G7

=MAX(0,
     MIN(G$6,$C7,IF($D7=$G$2,$E$2,$E$3)-1)- 
     MAX(EOMONTH(G$6,-1)+1,$B7)
     +1
    )*$E7 +
 MAX(0,
     MIN(G$6,$C7)- 
     MAX(EOMONTH(G$6,-1)+1,$B7,IF($D7=$G$2,$E$2,$E$3))
     +1
    )*$F7

gives results consistent with the correct values in the OP when extended to the other cells.

It is based on finding the start and end points of potentially overlapping periods of:

  • a calendar month
  • the period defined by the start and end dates in columns D and E
  • EITHER the period before the changeover date (given in range E2:E3) OR the period on or after the changeover date

The start point of any overlapping period is the latest of the 3 start points of the 3 individual periods. Similarly, the end point is the earliest of the 3 end points of the individual periods. If the start point is after the end point then there is no overlap.

Upvotes: 0

spacej3di
spacej3di

Reputation: 68

Figured it out

=IF($D7=$G$2,
$E7*SUMPRODUCT((--($B7+SEQUENCE(1+$C7-$B7,,0)<=G$6))*(--($B7+SEQUENCE(1+$C7-$B7,,0))>=(EOMONTH(G$6,-1)+1))*(--($B7+SEQUENCE(1+$C7-$B7,,0)<$E$2)))+
$F7*SUMPRODUCT((--($B7+SEQUENCE(1+$C7-$B7,,0)<=G$6))*(--($B7+SEQUENCE(1+$C7-$B7,,0))>=(EOMONTH(G$6,-1)+1))*(--($B7+SEQUENCE(1+$C7-$B7,,0)>=$E$2))),
$E7*SUMPRODUCT((--($B7+SEQUENCE(1+$C7-$B7,,0)<=G$6))*(--($B7+SEQUENCE(1+$C7-$B7,,0))>=(EOMONTH(G$6,-1)+1))*(--($B7+SEQUENCE(1+$C7-$B7,,0)<$E$3)))+
$F7*SUMPRODUCT((--($B7+SEQUENCE(1+$C7-$B7,,0)<=G$6))*(--($B7+SEQUENCE(1+$C7-$B7,,0))>=(EOMONTH(G$6,-1)+1))*(--($B7+SEQUENCE(1+$C7-$B7,,0)>=$E$3))))

SUMPRODUCT to the rescue. Created an array of active days with SEQUENCE using start/end dates. Filtered the array using SUMPRODUCT to count only days in the Month. Filtered the array again to count only days before Rate Change date. Multiplied it by Rate1. Repeated it but for days after and including Rate Change date. Multiplied it by Rate2. Used IF to tell it which Rate Change date to use.

Upvotes: 0

Related Questions