Date problem

  • Thread starter Thread starter Saylindara
  • Start date Start date
S

Saylindara

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
 
Brilliant! Thank you.

Mike H said:
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
Click to expand...
 
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
 
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?
 
Back
Top