LinkBack URL
About LinkBacks
Hello,
I need help creating a formula or macro that will compute the amount of unearned revenue each month based on the number of months and date.
A sample file is attached. Thank you for your time.
Annie
Monthly Unearned Revenue
Hello,
I need help creating a formula or macro that will compute the amount of unearned revenue each month based on the number of months and date.
A sample file is attached. Thank you for your time.
Annie
Please explain exactly what you are doing here, otherwise we are just guessing.
Show what data you are working with, what you expect, and where you need that answer to go
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
In F5 then copied.
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Re: Monthly Unearned Revenue
Hello,
Thank you again for all your help. I made some changes to the worksheet, and I am not sure how to fix the formula.
Changes:
Step 1 - Determine how many periods between the beginning and ending date.
Step 2 - Divide the number of periods by the invoice amount to arrive at the monthly revenue.
I have another question…
I used data valuation to create a customer drop-down list.
Unfortunately, I can only enter one customer at a time, which is time-consuming.
I will need to add customers in the future. I also tried entering blank cells in the drop-down list, but that did not work.
What do you suggest?
Thank you.
Updated worksheet attached.
In V5 on words it is shown as 22-Jan, 22Feb...so on.
Do you mean is it Jan year 2022, Feb year 2022 or 22nd jan 2021 ,22nd feb 2021. Because you have entered dates as 22-1-2021 , 22-2-2021.
Let me explain.
Since we are currently invoicing subscriptions with a starting date of June 2021, subscriptions over 12 months will continue into 2022.
Additional info:
I created a summary for 2021, with two months to go in 2022.
It may be better to start a worksheet for 2022 with a starting balance of 2000.
It has not been decided.
I hope I answered your question.
Thank you.
pl see file. I have changed dates in Row 4.
Format of cells in Row 4 changed to yy-mmm.
Formula copied up to Dec 2022. Further it can be extended to future years without change in formula.
In H5 then copied for all rows up to Dec 2022.
=IF($A5="","",IF((H$4>=$E5)*(H$4<=EOMONTH($E5,1*SUBSTITUTE($G5," Months","")-1)),$C5/(1*SUBSTITUTE($G5," Months","")),""))
Thank you.
I appreciate all your help.
Annie
Pl mark the thread solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks