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

"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 NOT 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

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

Hi bj,
I am still not getting it to work. What am I doing wrong?
example is:
For my start date column I need any dates prior to 1/04/05 to default to
1/04/05 and any start dates after 1/04/05 to stay as they are in that column.
My first cell in my start date column has the paste link formula =C2.
Based on your suggestion, how would I write it? I'm sorry I'm new to all
these formula's and even though have learnt a bit and tried different
formula's I'm not having much luck on this one.
Thanks Sue


"bj" wrote:

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

try
=if(c2datevalue("31/03/06"),datevalue("31/03/06"),max(datevalue("1/04/05"),C2)

on the chance that the input data is text that looks like dates rather than
actual dates you could also try

=if(datevalue(C2)datevalue("31/03/06"),datevalue("31/03/06"),max(datevalue("1/04/05"),datevalue(C2))
other things that might be happening. Depending on what version of Excel
you have, your dates may need to be in "mm/dd/yy" or "dd/mm/yy" format,
also in some versions of Excel, some of the commas in the formula may need to
be ";".

if you have mixed text and dates, you may have to convert all of the text
to dates.
If there are spaces associated with the text dates, you may need to use the
Trim() function before date value will work.
A simple way to check your input data is to select the column and
<format<cells<numberand play with different date formats and see if the
entire column changes. another simple way to check is to enter
=counta(C:C)-Count(C:C) in an unused Cell. If the value is greater than the
number of known labels in the column, you probably have some text which
looks like a date.

"Sue" wrote:

Hi bj,
I am still not getting it to work. What am I doing wrong?
example is:
For my start date column I need any dates prior to 1/04/05 to default to
1/04/05 and any start dates after 1/04/05 to stay as they are in that column.
My first cell in my start date column has the paste link formula =C2.
Based on your suggestion, how would I write it? I'm sorry I'm new to all
these formula's and even though have learnt a bit and tried different
formula's I'm not having much luck on this one.
Thanks Sue


"bj" wrote:

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

Hi bj,
Thanks so much. I'm now up and running. Great!
Sue

"bj" wrote:

try
=if(c2datevalue("31/03/06"),datevalue("31/03/06"),max(datevalue("1/04/05"),C2)

on the chance that the input data is text that looks like dates rather than
actual dates you could also try

=if(datevalue(C2)datevalue("31/03/06"),datevalue("31/03/06"),max(datevalue("1/04/05"),datevalue(C2))
other things that might be happening. Depending on what version of Excel
you have, your dates may need to be in "mm/dd/yy" or "dd/mm/yy" format,
also in some versions of Excel, some of the commas in the formula may need to
be ";".

if you have mixed text and dates, you may have to convert all of the text
to dates.
If there are spaces associated with the text dates, you may need to use the
Trim() function before date value will work.
A simple way to check your input data is to select the column and
<format<cells<numberand play with different date formats and see if the
entire column changes. another simple way to check is to enter
=counta(C:C)-Count(C:C) in an unused Cell. If the value is greater than the
number of known labels in the column, you probably have some text which
looks like a date.

"Sue" wrote:

Hi bj,
I am still not getting it to work. What am I doing wrong?
example is:
For my start date column I need any dates prior to 1/04/05 to default to
1/04/05 and any start dates after 1/04/05 to stay as they are in that column.
My first cell in my start date column has the paste link formula =C2.
Based on your suggestion, how would I write it? I'm sorry I'm new to all
these formula's and even though have learnt a bit and tried different
formula's I'm not having much luck on this one.
Thanks Sue


"bj" wrote:

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