How do I return the 10th day of the following month?

S

Statesman

From a "start date" in current month; I want to return a billing due date to
always be the 10th of the following month.
 
R

Ron Coderre

Try this:

A1: (a date)

This formula returns the 10th day of month after the month containing A1

B1: =DATE(YEAR(A1),MONTH(A1)+1,10)

Does that help?
***********
Regards,
Ron

XL2003, WinXP
 
S

Sandy Mann

Just another way:

=A1+33-DAY(A1+33)+10

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
R

Roger Govier

Hi Sandy

It works fine until A1 contains either the 30th or 31st of a month, when it
give the 10th of the month two months hence.
 
S

Sandy Mann

Thanks for the catch Roger. A way around it would be:

=A1-DAY(A1)+33-DAY(A1-DAY(A1)+33)+10

But that is using three function calls again so the OP would be as well to
use the DATE() formula although after that my original formula could be
used. However, I always prefer to use consistent formulas wherever
possible.

--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Statesman

This is GREAT!! Now I need to know how to roll the billing date to the 10th
of next month & year (ie: 01/10/07) when the current month is December (ie:
(12/08/06). Thanks!!
 
T

T. Valko

The DATE function is smart! It will automatically adjust the year.

A1 = 12/8/2006

=DATE(YEAR(A1),MONTH(A1)+1,10)

Returns: 1/10/2007
 
Top