Please can someone tell me if ther is a way (formula or macro) that I could
use to return a specific date (i.e 5th of Month - and year - e.g 5/10/06) for
any dates input in another cell

That is to say if I type 4/10/06 in one cell I can get a value of 5/10/06 in
the destiantion cell and if i input 6/11/06 in my cell i need to get value of
5/12/06 in my destination cell (working with UK date format).

Any help appreciated

=MIN(DATE(YEAR(A1),MONTH(A1)+{1,2},DAY(A1)*{1,0}))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"wbiggchiefy" wrote in message
...
Please can someone tell me if ther is a way (formula or macro) that I
could
use to return a specific date (i.e 5th of Month - and year - e.g 5/10/06)
for
any dates input in another cell

That is to say if I type 4/10/06 in one cell I can get a value of 5/10/06
in
the destiantion cell and if i input 6/11/06 in my cell i need to get value
of
5/12/06 in my destination cell (working with UK date format).

Any help appreciated

If you're inputting the date in A1 try

=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)5),5)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=510852

Thanks , That works perfectly. I'm impressed.

Chiefy.

"daddylonglegs" wrote:


If you're inputting the date in A1 try

=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)5),5)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=510852

Thanks for the reply Bob - it doesn't return the value I require - maybe my
question was misworded , however this will come in useful for something else
I am working on.

Chiefy.

"Bob Phillips" wrote:

=MIN(DATE(YEAR(A1),MONTH(A1)+{1,2},DAY(A1)*{1,0}))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"wbiggchiefy" wrote in message
...
Please can someone tell me if ther is a way (formula or macro) that I
could
use to return a specific date (i.e 5th of Month - and year - e.g 5/10/06)
for
any dates input in another cell

That is to say if I type 4/10/06 in one cell I can get a value of 5/10/06
in
the destiantion cell and if i input 6/11/06 in my cell i need to get value
of
5/12/06 in my destination cell (working with UK date format).

Any help appreciated