#1
|
|||
|
|||
Excel Formula Showing Revenue Recognition Based on Days and Months
I know I will earn $5,000 of monthly revenue from a client. $5,000 is represented in a monthly revenue cell. I have 12 columns showing the 12 months of the year. There is a cell showing the customers implementation date.
If a company’s implementation date is on or after the 15thof the month (example: 3/28/2014) then the next month (April 2014) is skipped and the $5,000 is returned to columns May through December. All months prior to May return $0.00. If the implementation date is before the 15th day of the month (example: 3/13/2014) then the next month (April 2014) and all months after will return $5,000. All months including March and prior must return $0.00. If the implementation date is unknown then 12/31/2099 would be in the implementation date cell and $0.00 is returned for all 12 months. Essentially, if the implementation date is prior to the 15th of the month the revenue will show as of the following month. If the implementation date is on or after the 15th of the month the revenue skips the following month and will show the month after. |
#2
|
|||
|
|||
use this formula
=IF(OR($B2=DATEVALUE("31/12/2099"),AND(DAY($B2)>=15,OR(DATE(YEAR($B2),MONTH($B 2),DAY(1))>=DATE(YEAR(D$1),MONTH(D$1),DAY(1)),DATE (YEAR($B2),MONTH($B2)+1,DAY(1))=DATE(YEAR(D$1),MON TH(D$1),DAY(1)))),AND(DAY($B2)<15,DATE(YEAR($B2),M ONTH($B2),DAY(1))>=DATE(YEAR(D$1),MONTH(D$1),DAY(1 )))),0,$C2) |
#3
|
||||
|
||||
See attached. The formula can be fairly simple.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
thats a much smarter way to do it , by changing the headings to the 15th , learn something on these forums every day
thanks |
#5
|
|||
|
|||
Great thanks
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula To Calculate Countdown Days To Project Completion | Catherine | Project | 1 | 02-19-2014 11:15 AM |
Formula for week days only | gbaker | Excel | 5 | 02-15-2014 01:07 AM |
showing calendar days by keeping normal workday calendar | ketanco | Project | 11 | 11-01-2013 08:27 AM |
Converting serial number to years, months and days | hannu | Excel | 6 | 02-14-2013 09:21 PM |
Setting up recurring months to skip some months etc. | dwelch@ykfireprevention.c | Outlook | 0 | 11-30-2010 10:15 AM |