try
=max(datevalue("1/04/05"),real date)
and =min(datevalue("31/03/06"),real date)

If you want to combine the two
=if(realdatedatevalue("31/03/06"),datevalue("31/03/06"),max(datevalue("1/04/05"),real date)

"Sue" wrote:

Does anyone know a formula that will change a date to 1/04/05 when it is
prior to that date eg. 1/04/04:31/03/05. It must be able to let through the
dates that fall after 1/04/05 eg. 1/04/05:31/03/06.
I need the same thing again but where the formula only alters the dates that
are after 31/03/06 eg. 1/04/06:31/03/07 and defaults them to 31/03/06 and
leaves alone any dates that are prior to 31/03/06 eg. 1/04/05:31/03/06.
Have had one suggestion but it does do what I need.
The dates come from paste link cells in another workbook.
Can anyone please help?
Formula tried so far:
if(link<startdate,startdate,if(linkfinishdate,fin ishdate,link))
Sue