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.

 

[RonB1111]

The =EDATE(C2,12/D2) was in response to post #3, so it only calculates the next billing date based on the 1st billing date.

The =EDATE(C2,DATEDIF(C2,TODAY(),"m")+12/D2-(MOD(DATEDIF(C2,TODAY(),"m"),12/D2))) works properly for me. Please verify C2 is formatted as date, and D2 is formatted as number (and not text)

 

[william11]

The =EDATE(C2,12/D2) was in response to post #3, so it only calculates the next billing date based on the 1st billing date.

The =EDATE(C2,DATEDIF(C2,TODAY(),"m")+12/D2-(MOD(DATEDIF(C2,TODAY(),"m"),12/D2))) works properly for me. Please verify C2 is formatted as date, and D2 is formatted as number (and not text)

This is my screen.. basically. The formatting for date/number is correct. I am at a loss. I will study up on DATEDIF and let you know if I figure it out. Any last thoughts are greatly appreciated.

=EDATE(I3,DATEDIF(I3,TODAY(),"m")+12/G3-(MOD(DATEDIF(I3,TODAY(),"m"),12/G3)))

[TABLE="width: 1238"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]

Customer[/TD]
[TD]Locker #[/TD]
[TD]Effective Date[/TD]
[TD]Locker2[/TD]
[TD]Lockers[/TD]
[TD]Billing Frequency[/TD]
[TD]Billing Cyle[/TD]
[TD]First Bill Date[/TD]
[TD]1st Bill[/TD]
[TD]Next Bill Date[/TD]
[TD]Next Bill Amount[/TD]
[TD]Month[/TD]
[TD]Annual[/TD]
[/TR]
[TR]
[TD]William[/TD]
[TD][/TD]
[TD="align: right"]8/1/2014[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD]1[/TD]
[TD]15th[/TD]
[TD="align: right"]8/15/2014[/TD]
[TD="align: right"]$148.50[/TD]
[TD="align: center"]#NUM![/TD]
[TD]$132.00[/TD]
[TD="align: right"]$33.00[/TD]
[TD="align: right"]$132.00[/TD]
[/TR]
[TR]
[TD]Alexandra[/TD]
[TD][/TD]
[TD="align: right"]8/1/2014[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD]2[/TD]
[TD]15th[/TD]
[TD="align: right"]8/15/2014[/TD]
[TD="align: right"]$82.50[/TD]
[TD="align: center"]#NUM![/TD]
[TD]$66.00[/TD]
[TD="align: right"]$33.00[/TD]
[TD="align: right"]$132.00[/TD]
[/TR]
[TR]
[TD]michael scott[/TD]
[TD][/TD]
[TD="align: right"]12/15/2014[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD]1[/TD]
[TD]1st[/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]$151.80[/TD]
[TD="align: center"]#NUM![/TD]
[TD]$132.00[/TD]
[TD="align: right"]$33.00[/TD]
[TD="align: right"]$132.00[/TD]
[/TR]
[TR]
[TD]pam beesley[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]1[/TD]
[TD]15th[/TD]
[TD="align: right"]1/15/1900[/TD]
[TD="align: right"]$0.00[/TD]
[TD][/TD]
[TD]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]1[/TD]
[TD]15th[/TD]
[TD="align: right"]1/15/1900[/TD]
[TD="align: right"]$0.00[/TD]
[TD][/TD]
[TD]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD]dwight schrute[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]1[/TD]
[TD]15th[/TD]
[TD="align: right"]1/15/1900[/TD]
[TD="align: right"]$0.00[/TD]
[TD][/TD]
[TD]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[RonB1111]

The problem is you're starting with "future dates" ie: 8/15/14 and 1/15/15 (today is 8/11/14). The formula will work if the First Bill date is earlier than TODAY().

 

[RonB1111]

We can combine the 2 formulas I gave to handle both past and future dates, like this:

=IF(I3>TODAY(),EDATE(I3,12/G3),EDATE(I3,DATEDIF(I3,TODAY(),"m")+12/G3-(MOD(DATEDIF(I3,TODAY(),"m"),12/G3))))

 

[Snakehips]

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


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.

@william11 Give or take the declared error, I thought that is exactly what I did?

Having read the subsequent posts I'm still confused as to some of the column references you are making.
In your more recent posts it looks to me as though First bill date is col H, Next bill date is J and Frequency is col F ?????

My solution below makes that assumption. If not correct then just alter the column references to suit.

@Ron Thanks for the heads up on EDATE. Despite some years of messing with Excel that wasn't previously on my radar.

Using EDATE I would now offer the solution below.

Having adjusted your latest formula, col O, to suit my assumed understanding of the data layout, there does appear to be the one discrepancy between our solutions, O5 ???

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1															Ron's Latest ?
2	Customer	#	Effective Date	L2	Lockers	Freq	Billing Cyle	First Bill Date	1st Bill	Next Bill Date	Amount	Month	Annual
3	William		01/07/2014	12	0	1	15th	15/07/2014	$148.50	15/07/2015	$132.00	$33.00	$132.00		15/07/2015
4	Alexandra		01/08/2014	12	0	2	1st	01/08/2014	$82.50	01/02/2015	$66.00	$33.00	$132.00		01/02/2015
5	michael scott		15/12/2014	12	0	1	1st	01/01/2015	$151.80	01/01/2015	$132.00	$33.00	$132.00		01/01/2016
6	pam beesley		01/07/2014	0	0	4	15th	15/07/2014	$0.00	15/10/2014	$0.00	$0.00	$0.00		15/10/2014
7	jim		22/02/2014	0	0	2	15th	01/03/2014	$0.00	01/09/2014	$0.00	$0.00	$0.00		01/09/2014
8	dwight schrute			0	0	1	15th		$0.00		$0.00	$0.00	$0.00		31/12/2014
Sheet3

Cell Formulas
Range	Formula
J3	=IF(H3="","",IF(H3),EDATE(H3,CEILING(DATEDIF(H3,TODAY(),"m")+1,12/F3)),H3))
O3	=IF(H3>TODAY(),EDATE(H3,12/F3),EDATE(H3,DATEDIF(H3,TODAY(),"m")+12/F3-(MOD(DATEDIF(H3,TODAY(),"m"),12/F3))))

 

[RonB1111]

Snakehips,
You're welcome on the EDATE(). I'm also a little confused on the OP's column references and use of future dates. Hopefully one of our formula's will give him what he's looking for. Regarding row #5 in your example, my interpretation is that Col J Next Bill Date should be the billing following the First Bill Date in Col H - using this logic, 01/01/2016 in my calc would be correct since it's 1 year after the 1st Bill Date of 01/01/2015. Also, I should have included handling of blanks in cell dates like you did.
Ron

 

[Snakehips]

Snakehips,
Regarding row #5 in your example, my interpretation is that Col J Next Bill Date should be the billing following the First Bill Date in Col H - using this logic, 01/01/2016 in my calc would be correct since it's 1 year after the 1st Bill Date of 01/01/2015.

Gotcha. I was working on the basis that if the first payment date is in the future then that is also the next payment date.

Hopefully OP will put us out of our misery shortly.

 

[william11]

We can combine the 2 formulas I gave to handle both past and future dates, like this:

=IF(I3>TODAY(),EDATE(I3,12/G3),EDATE(I3,DATEDIF(I3,TODAY(),"m")+12/G3-(MOD(DATEDIF(I3,TODAY(),"m"),12/G3))))

Perfect!! Thanks Ron you have been a huge help. I met my match with this equation and you came through with some serious knowledge.. much appreciative.


Snakehips, thanks for following up, it seems we both learned something new so that's nice... win win win.

cheers

 

[RonB1111]

You're welcome! Glad we finally got it.