# Periodic Billing

#### rod985

##### New Member
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

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
rod985 said:
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

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

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

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

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

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

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})

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)

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?}

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

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

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?

Replies
18
Views
671
Replies
0
Views
598
Replies
3
Views
307
Replies
4
Views
393
Replies
5
Views
312

1,206,827
Messages
6,075,099
Members
446,121
Latest member
Malikai

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back