Frequency of Billing

[Melo]

I am trying to find a formula that I can use to calculate the billing
frequency as a date. I have a contract start date and a contract end date
along with our billing frequency; such as monthly, quarterly, semi-annually,
and annually. What I need is a column that will tell me the next date the
contract will bill.

for example:
Contract Start Date 1/1/2007
Contract End Date 1/1/2009
Billing frequency Monthly

I want the column to put in that this contract will bill again on 1/1/2008

thank you for any help

 

[Bob Phillips]

=IF(AND(TODAY()>StartDate,TODAY()<EndDate),IF(BillingFrequency="Monthly",DATE(YEAR(TODAY()),
(BillingFrequency="Monthly")*(MONTH(TODAY())-MONTH(StartDate)+1)+
(BillingFrequency1="Quarterly")*(ROUNDUP((MONTH(TODAY())-MONTH(StartDate))/3,0)*3)+
+(BillingFrequency="Semi-annually")*(ROUNDUP((MONTH(TODAY())-MONTH(StartDate))/6,0)*6)+
+(BillingFrequency="Annually")*(ROUNDUP((MONTH(TODAY())-MONTH(StartDate))/12,0)*12),
DAY(StartDate))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Sorry that should have been

=IF(AND(TODAY()>StartDate,TODAY()<EndDate),DATE(YEAR(TODAY()),
(BillingFrequency="Monthly")*(MONTH(TODAY())-MONTH(StartDate)+1)+
(BillingFrequency="Quarterly")*(ROUNDUP((MONTH(TODAY())-MONTH(StartDate))/3,0)*3)+
(BillingFrequency="Semi-annually")*(ROUNDUP((MONTH(TODAY())-MONTH(StartDate))/6,0)*6)+
(BillingFrequency="Annually")*(ROUNDUP((MONTH(TODAY())-MONTH(StartDate))/12,0)*12),
DAY(StartDate)),"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)