Date Issue

[william11]

Hi, and thank you for reading.

Billing frequency (B1) is either quarterly (4), semi-annual(2), or yearly(1) from drop-down list. I have a cell (A1) for the date when my contract begins and C1 would be the first bill date, in another cell (D1) I would like to display the next bill due for each user. I also want D1 to auto-update based on current date and add either 3 months, 6 months, or a year to D1 when 'next bill date' has already occurred.

A B C D
|Start date | Billing Frequency | First Bill Date | Next Bill Date|



So I was thinking something like this:

IF(AND(B1=1, than auto-update D1 yearly, IF(B1=2, then auto-update D1 semi-annual, IF(B1=4, then auto-update D1 quarterly

and I guess somehow this needs to correspond with TODAY() in order to update itself.

 

[Snakehips]

william11,

This is not perfect but maybe it's workable for you.
As you can see from below, now and again depending upon leap year or not and the actual month first billed and the today month, there can be a one or two day error.
It is not cumulative. two days should be the max.

Excel 2007
	A	B	C	D
1	Start	Freq	First Bill	Next Bill
2	01/01/2006	4	06/02/2006	06/11/2014
3	01/01/2013	2	30/09/2013	30/09/2014
4	01/01/2010	1	30/01/2010	30/01/2015
5	01/08/2014	2	30/08/2014	30/08/2014
6	01/07/2009	1	30/07/2009	30/07/2015
7	01/07/2014	2	30/07/2014	28/01/2015
8	02/06/2014	2	30/06/2014	29/12/2014
9	03/10/2013	4	30/10/2008	30/10/2014
Sheet2

Cell Formulas
Range	Formula
D2	=TODAY() +(365.25/B2)-(MOD(TODAY()-C2,365.25/B2))

Hope that helps.

 

[william11]

Thank you for the reply Snakehips. I was hoping for a formula that would display the desired dates in cell D

	A	B	C	D
1	Start	Freq	First Bill	Next Bill
2	01/01/2006	4	01/15/2006	04/15/2006
3	01/01/2006	2	01/15/2006	07/15/2006
4	01/16/2006	1	02/1/2006	02/01/2007

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Please only consider column C and how to apply column B in order to get the result in column D. Also, this will need to auto-update another year, half year, or 3 months when date in column D has been reached.

Thank you for your help.

 

[RonB1111]

Try this in D2 and copy down.

=EDATE(C2,DATEDIF(C2,TODAY(),"m")+12/D2-(MOD(DATEDIF(C2,TODAY(),"m"),12/D2)))

It's based on today's date on the assumption that past billings have already been billed. Please advise if this is not what you want.

To get Col D in your 2nd post, try:
=EDATE(C2,12/D2)

 

[william11]

Customer	Locker #	Effective Date	Locker2		Billing Frequency	Billing Cyle	First Bill Date	1st Bill	Next Bill Date	Next Bill Amount	Month	Annual
William		8/1/2014	12	0	1	15th	8/15/2014	$148.50		$132.00	$33.00	$132.00
Alexandra		8/1/2014	12	0	2	15th	8/15/2014	$82.50		$66.00	$33.00	$132.00
michael scott		8/15/2014	12	0	4	1st	9/1/2014	$52.80		$33.00	$33.00	$132.00

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

In this example "Next Bill Date" would be:

8/15/2015 (add 12 months to 8/15/2014)
2/15/2015 (add 6 months to 8/15/2014)
12/1/2014 (add 3 months to 9/1/2014)

 

[william11]

I pasted the equation


=EDATE(C2,DATEDIF(C2,TODAY(),"m")+12/D2-(MOD(DATEDIF(C2,TODAY(),"m"),12/D2)))</pre>
in D2, but the equation contains 'D2' and I am getting a 'circular reference' error. Am I missing something?

Thank you so much I have spent more time than I would like to admit trying to figure this equation.

 

[RonB1111]

You probably didn't see the last part of my post which I edited. Try this formula:
=EDATE(C2,12/D2)

 

[william11]

Hi Ron, could you check my last two posts and advise? I want to put the equation in 'NextBillDate' column D (circular reference to have D in equation?) and be able to drag it down to effect all 'Customers'. So the equation would need to be able to recognize/respond to either the 3 month, 6 month, or 12 month 'billing frequency' designation.

 

[RonB1111]

Change the 2 instances of D2 to the column that contains the "Billing Frequency" (maybe F2 in your layout). In my test data I had the date in C2 and the Billing Frequency in D2 and the formula in E2. Sorry about that. So does the 1st longer formula give you what you're looking for of did the 2nd shorter formula work for you?

 

[william11]

Change the 2 instances of D2 to the column that contains the "Billing Frequency" (maybe F2 in your layout). In my test data I had the date in C2 and the Billing Frequency in D2 and the formula in E2. Sorry about that. So does the 1st longer formula give you what you're looking for of did the 2nd shorter formula work for you?

=EDATE(C2,12/D2)

The above equation works but my concern is that 'next bill date' will not auto-update as the months/years pass. (I changed the date on my computer and 'next bill date' did not update to what would be the next future billing date; =today() confirmed excel did recognize my manual date change)


=EDATE(C2,DATEDIF(C2,TODAY(),"m")+12/D2-(MOD(DATEDIF(C2,TODAY(),"m"),12/D2)))

The above equation returns a #NUM! error. C2 is my 'effective date' and D2 is 'billing frequency'