Mark asked:-
I would like to have an Excel formula where as

C6=(TODAY)
D6=7/21/09
A6 =D6-C6   but....

A6 counts down the days from the 21st of the month. What I
want it to do is when A6 reaches 0, it changes D6 by
increasing the month by one.
------------------
This works with the UK date style of D/M/Y

Not only does the month have to change in D6 but the year 
will change when you get to December.

The following can be any where on sheet, out of the way of 
the data on view. I have used
I6   =TODAY()+12
The + 12 is to make sure that after 21st of the month you 
are in the "next" month.

J6   =IF(DAY(C6)<21,MONTH(C6),MONTH(I6))
When "today" is greater than 21, then the next month is 
used.

K6  =IF(DAY(C6)<21,YEAR(C6),YEAR(I6))
Only used in December but makes sure that the year 
increments in that month.

The above 3 cells are used in 
D6  =CONCATENATE(21,"/",J6,"/",K6)
Picking up the 21st, which is fixed and then the month and 
year as appropriate.

Fortunately, even when a date is "concatenated" it is 
treated as a "real" date and a calculation can be done on 
it. 

I have not tried it on a computer set up for M/D/Y but 
would hope that swapping the 21 and J6 as below would make 
it work for the US style of date.
D6  =CONCATENATE(J6,"/",21,"/",K6)

Regards

Tony
Leicestershire, UK

             Do you want to signoff PCSOFT or just change to
                    Digest mode - visit our web site:
                   http://freepctech.com/pcsoft.shtml