# Thread: When is the next 10th of the month ?

1. I had to write write a ridiculously complicated formula (I didn't want to use VBA on this occasion) to identify the next date which is the 10th of a month. The reason is that's when a payment is made into an account.

The answer I am looking for is:

If the date is today: 10 October 2010

If the date is 11 October 2010: 10 November 2010

If the date is 20 December 2010: 10 January 2011

If the date is 10 February 2011: 10 February 2011 (or, in my dreams, "Today" but I had lost the will to live at this level of nested IF statements !)

Is there an elegant way of extracting the date when the "10th of a month" occurs ?

This is just for the sake of tidiness and readability of the formula.

Many thanks

Martin

2. If the date is in A1, this seems to work:
=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>10),10)

Steve

3. That is simply brilliant !

I had not appreciated that using "month 13" increments the year.

Thanks

4. Not only does Month 13 (as well as any month past 12) increment the year, but days past 31 will increment the month. In the same way, days <1 will decrement the month and months <1 will decrement the year. [Day 0 is the last day of the previous month]

Steve

5. Yes, I'd experimented and worked that out after your first answer - funny how one small hint can lead to many solutions, but without it you can be stuck.

Thanks again.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•