Log In
Register
Home
Active Member
January 27, 2024
Offline
Hi,
Please help me with simplified excel formula to calculate the straight line monthly equal revenue, having start date and end date.
Here both Start and End dates are inclusive, and each month must have equal revenue, except for the partial start month and partial end month.
Trusted Members
October 17, 2018
Offline
How about more information like Excel version and a asample file?
Guessing is not an option to obtain relevant answers
Active Member
January 27, 2024
Offline
Hi, I am using Office 365, latest version (Microsoft® Excel® 2021 MSO (Version 2312 Build 16.0.17126.20132) 64-bit).
I can provide the further info, if needed.
Active Member
January 27, 2024
Offline
Hi, I’m using office Microsoft 365 Excel 2021 MSO (Version 2312 Build 16.0.17126.20132) 64-bit).
Moderators
January 31, 2022
Offline
Yes please, add more information. For instance, upload a file with examples of what you are dealing with and the desired results.
Active Member
January 27, 2024
Offline
Hi,
Please find excel file. This file has the formula, which i am using to calculate the monthly SLM revenue. Here i need simplified formula to calculate month equal revenue (irrespective of no of days in the month; incase of partial month, partial equal monthly revenue is needed; also both start and end date should be included).
I can provide more inf, if needed.
Sample formula:
=MAX(MIN(((TEXT(D$1,"yy")-TEXT($A3,"yy"))*12+(TEXT(D$1,"mm")-TEXT($A3,"mm"))+(EOMONTH($A3,0)-$A3+1)/(EOMONTH($A3,0)-(EOMONTH($A3,-1))))/((TEXT($B3,"yy")-TEXT($A3,"yy"))*12+(TEXT($B3,"mm")-TEXT($A3,"mm"))-1+(EOMONTH($A3,0)-$A3+1)/(EOMONTH($A3,0)-(EOMONTH($A3,-1)))+($B3-EOMONTH($B3,-1))/(EOMONTH($B3,0)-EOMONTH($B3,-1))),1),0)*$C3
1 Guest(s)
