Periodic Billing

[rod985]

I own a web design/hosting company, and am setting up a spreadsheet which will tell me when each client's next billing date is. Each client has separate terms with us; some are billed once a month, some quarterly, and some annually. So if I put this periodic information into one column and the billing start date in another column, how can I have the last column read out the next billing date based on that information?

Thanks!

Anthony

 

[Brian from Maui]

I own a web design/hosting company, and am setting up a spreadsheet which will tell me when each client's next billing date is. Each client has separate terms with us; some are billed once a month, some quarterly, and some annually. So if I put this periodic information into one column and the billing start date in another column, how can I have the last column read out the next billing date based on that information?

Thanks!

Anthony

To add months

=DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))

Change the 3 to a cell reference, to add a year +1 after the year

 

[rod985]

If the billing start date is 1/1/2004, and the client is billed every 3 months, the next billing date as of today, Feb. 8, would be 4/1/2005. So, the next billing date needs to be generated based on the billing start date, today's date, and the period (every month/quarter/year).

Anybody know how to do this?

Anthony

 

[Scott Huish]

Assuming the billing start date is in A2, give this a try:

=DATE(YEAR(NOW()),LOOKUP(MONTH(NOW()),{1,4;4,7;7,10;10,1})+MOD(MONTH(A2)+2,3),1)

 

[Alisa]

Hotpepper, I just found this - it is does exactly what I want, but now I am curious: can you (or anybody) explain how this part of the formula works?

LOOKUP(MONTH(NOW()),{1,4;4,7;7,10;10,1})

 

[Scott Huish]

It's a lookup on the month value of NOW() along with the rest of the formula (up until the ,1) for the month part of the DATE function.

so months 1-3 would generate a value of 4 (April)
months 4-6 would generate a value of 7 (July)
months 7-9 would generate a value of 10 (October)
and months 10-12 would generate value of 1 (January)

 

[Alisa]

Thanks for the explanation Hotpepper, So now I am trying to figure out how to modify this to calculate biweekly dates - for instance, if the original date is March 4, the next date (based on todays date) is April 1. Is there a way to use lookup(week(now()),{but what would go in here?}

 

[Scott Huish]

I'm assuming it's a little more complicated than just:
=A2+28

if so, what are you basing the next date on?

 

[Alisa]

Well, if the original date is March 4, then the next billing date would be March 18, but the date I actually want to return out of the formula is the next billing date that will occur after today's date-in this case April 1. So, like the OPs question with quarterly billing, I want the next billing date based on the billing start date, todays date, and the period (biweekly). My fallback solution is to just create a list on another worksheet that has all the dates (i.e. 3/4, 3/18, 4/1, 4/15, 4/29, etc.) and then have my formula lookup the first date that is after today, but I like the idea of having a formula that could figure that out without having the list somewhere. Hopefully that makes sense?