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.

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)

See attached. The formula can be fairly simple.

Attached Files

Income Stream.xlsx (9.7 KB, 49 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

thats a much smarter way to do it , by changing the headings to the 15th , learn something on these forums every day
thanks

Great thanks