ChetanPuri
Board Regular
- Joined
- Sep 5, 2018
- Messages
- 63
- Office Version
- 365
- Platform
- Windows
Good Afternoon Excel Expert team,
Need some help regarding Revenue Allocation across Month Formula, I am trying to create formula which Calculate the amount in K3, first based on Number of days between Column I3 Start Date and Column J3 End Date, then in Column R3, It should refer to EOM Dates on Column R2 to AC2 and allocate Revenue based on number of days in that month for example 158.71* 31 Days or 158.71*28 etc. There is one more criteria that I need to consider is in Column R 1 and S1, I have a Start date of the Financial Year and in S1 I have the month end Date. I need a formula where the Daily amount is based on Start and End Date of the contract but should only calculate up to EOM column as per date in S1. Thank you for all your help in advance
Need some help regarding Revenue Allocation across Month Formula, I am trying to create formula which Calculate the amount in K3, first based on Number of days between Column I3 Start Date and Column J3 End Date, then in Column R3, It should refer to EOM Dates on Column R2 to AC2 and allocate Revenue based on number of days in that month for example 158.71* 31 Days or 158.71*28 etc. There is one more criteria that I need to consider is in Column R 1 and S1, I have a Start date of the Financial Year and in S1 I have the month end Date. I need a formula where the Daily amount is based on Start and End Date of the contract but should only calculate up to EOM column as per date in S1. Thank you for all your help in advance
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| S1 | S1 | ='3 Way Rec - 2'!E1 |
| R2:AC2 | R2 | ='3 Way Rec - 2'!C3 |
| P3:P14 | P3 | =J3-I3+1 |
| Q3:Q14 | Q3 | =K3/P3 |
| R3:AC3 | R3 | =IFERROR(($K3/($J3-$I3+1))*(EOMONTH(MIN(MAX($I3,$R$1),$S$1),0)-MAX($I3,$R$1)+1),"") |
| R4:AC14 | R4 | =IFERROR($Q4*((MAX(R$2-$I4,0)-MAX(EOMONTH(R$2,-1)-$I4,0))-(MAX(R$2-$S$1,0)-MAX(EOMONTH(R$2,-1)-$S$1,0))+(EOMONTH(R$2,0)=EOMONTH($I4,0))),"") |
