I want to find all the dates from a list that are earlier than a specific
date and replace them with another date. How do I do that without having to
do one by one?

As an example in cell C10:
=IF(B10<DATE(2000,1,1),DATE(2001,1,1),B10)

"Jose Martinez" wrote:

I want to find all the dates from a list that are earlier than a specific
date and replace them with another date. How do I do that without having to
do one by one?

That's helpful if the values are to be transfered to a different cell (in
this case from cell B10 to C10). Is there a function similar to Conditional
Formating which instead of changing the format would change the values only?

"Martin P" wrote:

As an example in cell C10:
=IF(B10<DATE(2000,1,1),DATE(2001,1,1),B10)

"Jose Martinez" wrote:

I want to find all the dates from a list that are earlier than a specific
date and replace them with another date. How do I do that without having to
do one by one?

Conditional formatting does not have the problem of circular functions.
If your purpose is to save work, say you have a few hundred dates, you could
copy the cells in column C and use paste special with values checked to
replace the cells in column B.
Another possibility is to use Edit Replace to change the relevant dates to
Bold, copy all the cells to Word and then replace everything that is bold
with 01/01/2001 (or whichever format you are using). Then copy back to Excel.

"Jose M" wrote:

That's helpful if the values are to be transfered to a different cell (in
this case from cell B10 to C10). Is there a function similar to Conditional
Formating which instead of changing the format would change the values only? Since you said "without having to copy them one by one" I thought you had this in mind.
You could also use Find and Replace to change the relevant cells to Bold.

"Martin P" wrote:

As an example in cell C10:
=IF(B10<DATE(2000,1,1),DATE(2001,1,1),B10)

"Jose Martinez" wrote:

I want to find all the dates from a list that are earlier than a specific
date and replace them with another date. How do I do that without having to
do one by one?

Correction: use conditional formatting to change the cells to bold.

"Martin P" wrote:

Conditional formatting does not have the problem of circular functions.
If your purpose is to save work, say you have a few hundred dates, you could
copy the cells in column C and use paste special with values checked to
replace the cells in column B.
Another possibility is to use Edit Replace to change the relevant dates to
Bold, copy all the cells to Word and then replace everything that is bold
with 01/01/2001 (or whichever format you are using). Then copy back to Excel.

"Jose M" wrote:

That's helpful if the values are to be transfered to a different cell (in
this case from cell B10 to C10). Is there a function similar to Conditional
Formating which instead of changing the format would change the values only? Since you said "without having to copy them one by one" I thought you had this in mind.
You could also use Find and Replace to change the relevant cells to Bold.

"Martin P" wrote:

As an example in cell C10:
=IF(B10<DATE(2000,1,1),DATE(2001,1,1),B10)

"Jose Martinez" wrote:

I want to find all the dates from a list that are earlier than a specific
date and replace them with another date. How do I do that without having to
do one by one?