Solved Excel bill date if/then formula

Microsoft Excel for mac 2011 - macintosh
January 30, 2012 at 08:06:44
Specs: Macintosh
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!


See More: Excel bill date if/then formula

Report •


#1
January 30, 2012 at 08:46:15
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. (June 9, 2012 sign up becomes July 9, 2012 bill date)

In addition, you said: "...a bill date, which always falls on the 9th of 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 always falls 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.


Report •

#2
January 30, 2012 at 09:19:58
Thanks for the prompt reply, and I apologize for any confusion. I have revised the original post to clarify my intent with this formula.

Report •

#3
January 30, 2012 at 09:27:34
✔ Best Answer
=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.


Report •

Related Solutions

#4
January 31, 2012 at 07:56:56
Many thanks!!!!

Report •


Ask Question