Need formula that provides deferred revenue for SaS maintenance

Hello,

Please help with my problem I am trying to create a revenue recognition schedule for SAS maintenance. It is based on invoice date/start date/recognition month and dollars. My problem is the recognition of dollars in the month it belongs (if the invoice date is after the start date then the dollars the billed month must reflect the current month dollars and the months that should have been previously recognized).

Thank you in advance for any help.

Glenn,

The formula is not showing the dollars in the correct months (ex. look at the first line 2 on the attached sheet).

Thanks any help would be appreciated.

Glenn,

Sorry for any confusion.

Example: Row 2 - what I am trying to do is create a formula that starts with the Service date (column D) depending on when the line was Created (column C). Dollars should start in column BO (July18 - Service date) and should be $103.33 each month for July18 - June19.

Example: Row 8 - Dollars should start in column BN (June18 - Created on date) and should be $131.00 ($32.75*4) in June18 and July18-Feburary19 $32.75 each month.

I want a formula the can take both situations into account.

Thanks for any help you can give.

Glenn,

Sorry it took me so long to get back to you. The formula is great and works well, thank you very much for you time.

Hey Glenn,

Since you were so helpful to me back in 2018, I have a similar problem however using the same method but the start date and end date can be any day of the month and it of course taking into consideration the created on date.
So what I need is a formula that captures 365 days per year with dollars based on number of days in each month and if service starts ex. $1,000/365 days - 2/4/22 and ends 2/3/23 that Feb would have $65.75, March $84.93 (31 dys in March), April $82.19 (30 dys in April), etc. Now say the order comes in with these dates but it doesn't come in until ex. 3/15/22 so I would expect to see: March dollars $150.68, because once books are closed you cannot go back so if invoice in March they amount capture in March would be what happened in Feb and March22.

Thanks,
Elisa

Below is the formula that you created for me that works perfectly for monthly with start/stop dates being whole months, while taking into consideration the invoice date and how to bucket the dollars monthly:
=IF($D2<$C2,IF(EOMONTH($C2,0)=AH$1,COUNTIFS(AH$1:$AH$1,">="&$D2,AH$1:$AH$1,"<="&EOMONTH($C2,0))*$AA2,IF(AND($C2<=EOMONTH(AH$1,-1)+1,$AA2,EOMONTH($D2,$S2)>AH$1),$AA2,"")),IF(AND($D2<=EOMONTH(AH$1,-1)+1,$AA2,EOMONTH($D2,$S2)>AH$1),$AA2,""))

I would really appreciate you help if you are still up for it.