Hello, I am trying to construct two excel formulas to help determine a bill date, which always falls on the 9th of the month. Sign up dates occur throughout the year.

In column AV, I have a set of dates that represent signups.

The bill date should be as follows.

1. If the sign-up date in AV2 is prior to the 9th, make the bill date in AW2 be the 9th of that same month. (June 3, 2012 sign up becomes June 9, 2012 bill date.)

2. If the sign-up date in AV2 is after the 9th of the month, make the bill date in AW2 be the 9th of the following month. (June 10, 2012 sign up becomes July 9, 2012 bill date.)

3. If the sign-up date in AV2 is the 9th of the month, make the bill date in AW2 the 9th the following month. (June 9, 2012 sign up becomes July 9, 2012 bill date.)Any help would be much appreciated! I am no Excel expert, but I do have "some" of the formula possibly figured out.

=IF(AV2<="DATE(9)"

Any help would be much appreciated!

The layout of your question doesn't make sense. You've got bullet 3 before bullet 2.

Bullet 2 says to use the same month if the sign up date is the 9th, but your example uses the following month.

(June9, 2012 sign up becomesJuly9, 2012 bill date)In addition, you said:

"...a bill date, which always falls on the".9thof the month"Then, under bullet 3 you said:

"June 10, 2012 sign up becomes July.10, 2012 bill date"How can the bill date be the 10th if the bill date

alwaysfalls on the 9th?It wouldn't make sense to offer a solution to a question that we don't quite understand.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks for the prompt reply, and I apologize for any confusion. I have revised the original post to clarify my intent with this formula.

=IF(DAY(AV2)<9,DATE(YEAR(AV2),MONTH(AV2),9),DATE(YEAR(AV2),MONTH(AV2)+1,9))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Many thanks!!!!

Ask Your Question

Weekly Poll