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

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

--
HTH

Bob

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

"Melo" wrote in message
...
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

Sorry that should have been

=IF(AND(TODAY()StartDate,TODAY()<EndDate),DATE(YE AR(TODAY()),
(BillingFrequency="Monthly")*(MONTH(TODAY())-MONTH(StartDate)+1)+
(BillingFrequency="Quarterly")*(ROUNDUP((MONTH(TOD AY())-MONTH(StartDate))/3,0)*3)+
(BillingFrequency="Semi-annually")*(ROUNDUP((MONTH(TODAY())-MONTH(StartDate))/6,0)*6)+
(BillingFrequency="Annually")*(ROUNDUP((MONTH(TODA Y())-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" wrote in message
...
=IF(AND(TODAY()StartDate,TODAY()<EndDate),IF(Bill ingFrequency="Monthly",DATE(YEAR(TODAY()),
(BillingFrequency="Monthly")*(MONTH(TODAY())-MONTH(StartDate)+1)+
(BillingFrequency1="Quarterly")*(ROUNDUP((MONTH(TO DAY())-MONTH(StartDate))/3,0)*3)+
+(BillingFrequency="Semi-annually")*(ROUNDUP((MONTH(TODAY())-MONTH(StartDate))/6,0)*6)+
+(BillingFrequency="Annually")*(ROUNDUP((MONTH(TOD AY())-MONTH(StartDate))/12,0)*12),
DAY(StartDate))))

--
HTH

Bob

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

"Melo" wrote in message
...
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