Date Ranges

[Guest]

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

 

[Bob Phillips]

=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)

 

[daddylonglegs]

If you're inputting the date in A1 try

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

 

[Guest]

Thanks , That works perfectly. I'm impressed.

Chiefy.

 

[Guest]

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.