I'm preparing an employee course attendance list. There is a column for date
course attended and another for when it needs to be updated using format for
adding one, 2 or whatever years to the course attended column (there are lots
of courses). At the moment the course attended columns are blank (it will
take me a while to fill in the dates). The course update column shows
31/12/1900. As I add the date the employee attends a course this changes to
the correct update date. But it is driving me mad that I can't make
31/12/1900 disappear when the cell that the formula is based on is blank . I
have tried using conditional formatting. I have tried just pasting the
formula and not the value but to no avail. What can I do?

Hi,

This checks b2 and adds 2 years to the date or does nothing if B2 is empty

=IF(B2="","",DATE(YEAR(B2)+2,MONTH(B2),DAY(B2)))

Mike

"Saylindara" wrote:

I'm preparing an employee course attendance list. There is a column for date
course attended and another for when it needs to be updated using format for
adding one, 2 or whatever years to the course attended column (there are lots
of courses). At the moment the course attended columns are blank (it will
take me a while to fill in the dates). The course update column shows
31/12/1900. As I add the date the employee attends a course this changes to
the correct update date. But it is driving me mad that I can't make
31/12/1900 disappear when the cell that the formula is based on is blank . I
have tried using conditional formatting. I have tried just pasting the
formula and not the value but to no avail. What can I do?

Brilliant! Thank you.

"Mike H" wrote:

Hi,

This checks b2 and adds 2 years to the date or does nothing if B2 is empty

=IF(B2="","",DATE(YEAR(B2)+2,MONTH(B2),DAY(B2)))

Mike

"Saylindara" wrote:

I'm preparing an employee course attendance list. There is a column for date
course attended and another for when it needs to be updated using format for
adding one, 2 or whatever years to the course attended column (there are lots
of courses). At the moment the course attended columns are blank (it will
take me a while to fill in the dates). The course update column shows
31/12/1900. As I add the date the employee attends a course this changes to
the correct update date. But it is driving me mad that I can't make
31/12/1900 disappear when the cell that the formula is based on is blank . I
have tried using conditional formatting. I have tried just pasting the
formula and not the value but to no avail. What can I do?

Hi,

Suppose the start date is in cell A1 then in B1 enter the formula

=IF(A1,EDATE(A1,24),"")

To take advantage of the EDATE function in 2003 or earlier you must attach
the Analysis ToolPak - choose Tools, Add-Ins and check the Analysis ToolPak.

Cheers,
Shane Devenshire

"Saylindara" wrote in message
...
I'm preparing an employee course attendance list. There is a column for
date
course attended and another for when it needs to be updated using format
for
adding one, 2 or whatever years to the course attended column (there are
lots
of courses). At the moment the course attended columns are blank (it will
take me a while to fill in the dates). The course update column shows
31/12/1900. As I add the date the employee attends a course this changes
to
the correct update date. But it is driving me mad that I can't make
31/12/1900 disappear when the cell that the formula is based on is blank .
I
have tried using conditional formatting. I have tried just pasting the
formula and not the value but to no avail. What can I do?

That's a nice solution too. This spreadsheet will go on our intranet. Will
all users (who will just be able to read only) have to have the ToolPak
attached too?

"Shane Devenshire" wrote:

Hi,

Suppose the start date is in cell A1 then in B1 enter the formula

=IF(A1,EDATE(A1,24),"")

To take advantage of the EDATE function in 2003 or earlier you must attach
the Analysis ToolPak - choose Tools, Add-Ins and check the Analysis ToolPak.

Cheers,
Shane Devenshire

"Saylindara" wrote in message
...
I'm preparing an employee course attendance list. There is a column for
date
course attended and another for when it needs to be updated using format
for
adding one, 2 or whatever years to the course attended column (there are
lots
of courses). At the moment the course attended columns are blank (it will
take me a while to fill in the dates). The course update column shows
31/12/1900. As I add the date the employee attends a course this changes
to
the correct update date. But it is driving me mad that I can't make
31/12/1900 disappear when the cell that the formula is based on is blank .
I
have tried using conditional formatting. I have tried just pasting the
formula and not the value but to no avail. What can I do?