I need to be able to enter a day of the month (1st, 5th, 10th, 15th, 20th,
25th) and get the next available date with that day in it. confusing.... ok
if I put a 5 in a cell today , I need another cell to come up with 3/5/09 .
If I put a 5 in a cell on 4/10/09, the other cell should come up with 5/5/09
and so on for each of the available payment dates. Did I give enough
information? Thanks

If I understand your question, I think the following formula will do
what you want. It assumes the number (5, 10, 15, etc) is in cell A1.

=IF(A1<DAY(TODAY()),DATE(YEAR(TODAY()),MONTH(TODAY ())+1,A1),DATE(YEAR(TODAY()),MONTH(TODAY()),A1))

If A1 is less than (earlier) the day today, it returns the A1th day of
next month. If A1 is greater than or equal to (later than) the day
today, it return the A1th day of this month.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Tue, 17 Feb 2009 13:16:09 -0800, tara
wrote:

I need to be able to enter a day of the month (1st, 5th, 10th, 15th, 20th,
25th) and get the next available date with that day in it. confusing.... ok
if I put a 5 in a cell today , I need another cell to come up with 3/5/09 .
If I put a 5 in a cell on 4/10/09, the other cell should come up with 5/5/09
and so on for each of the available payment dates. Did I give enough
information? Thanks

=DATE(YEAR(TODAY()),MONTH(TODAY())+IF(DAY(TODAY()) =A1,1,0),A1)

Check the = ; you may need just , I don't know

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"tara" wrote in message
...
I need to be able to enter a day of the month (1st, 5th, 10th, 15th, 20th,
25th) and get the next available date with that day in it. confusing....
ok
if I put a 5 in a cell today , I need another cell to come up with 3/5/09
.
If I put a 5 in a cell on 4/10/09, the other cell should come up with
5/5/09
and so on for each of the available payment dates. Did I give enough
information? Thanks

Thanks a lot, it works perfect.

"Niek Otten" wrote:

=DATE(YEAR(TODAY()),MONTH(TODAY())+IF(DAY(TODAY()) =A1,1,0),A1)

Check the = ; you may need just , I don't know

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"tara" wrote in message
...
I need to be able to enter a day of the month (1st, 5th, 10th, 15th, 20th,
25th) and get the next available date with that day in it. confusing....
ok
if I put a 5 in a cell today , I need another cell to come up with 3/5/09
.
If I put a 5 in a cell on 4/10/09, the other cell should come up with
5/5/09
and so on for each of the available payment dates. Did I give enough
information? Thanks